﻿
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [sp_EstActVA_Grouping_Web]
@AcademicYearID VARCHAR(5),
@Grouping1 VARCHAR(max), 
@Grouping2 VARCHAR(max), 
@Grouping3 VARCHAR(max), 
@Grouping4 VARCHAR(max),
@SavedViewID INTEGER,
@Grouping1Filter VARCHAR(max) = NULL

AS
--line 969

--exec sp_EstActVA_Grouping_web '19/20','CourseCode','CourseTitle','''''','''''',22
--exec sp_EstActVA_Grouping_web '19/20','EnrolmentUserDefinedNumber2','''''','''''','''''',0
--exec sp_EstActVA_Grouping_web '19/20','StudentUserDefinedNumber2','EnrolmentUserDefinedNumber','''''','''''',0
--exec sp_EstActVA_Grouping_web '23/24','TeachingGroupCode','''''','''''','''''',22,'Q41AMG'
--exec sp_EstActVA_Grouping_web '23/24','CourseCode','''''','''''','''''',22

-- =============================================
-- Author:		Andrew Genner
-- Create date: AMG 23/04/2020 v1.22.0
-- Descripton:	SP is the same as sp_EstActVA_Grouping - Speeded up by using CASE statements to only do the calculations that are 
--				necessary and stripped out approx 30% of the fields we are not going to be showing in web
-- Modified:	AMG 16/06/2022 v1.32
-- Description:	Added InYear Points 9 - 12
--				Added in GroupingIYMostRecent_vs_RecentTarget_NationalBanding and GroupingEnrolmentGradeVAScore_vs_MostRecent_NationalBanding
-- Modified:	AMG 20/10/2023 v1.36
-- Description:	Added @AcademicYearID into the @SQL1 dynamic SQL to populate #tStudent
-- Modified:	AMG 23/11/2023
-- Description:	Added UniqueStudentCount so we can calculate the Average QOE score PER STUDENT, not based on average of all Enrolments.
-- Modified:	AMG 04/12/2023
-- Description:	This wasn't working for Manually Created Groups, so now it uses the OriginalCourseID to work out which EstActVA records it needs.
-- Modified:	AMG 09/01/2024 v1.36.0
-- Description: The PARTITION on the UniqueStudentCount wasn't including the "QualificationType" - now corrected
-- Modified:	AMG 23/01/2024 v1.37
-- Description:	When "TeachingGroupCode" is passed through, it always fills #TempIDs table now to better handle Adhoc Groups.
-- Modified:	AMG 30/04/2024 v1.38
-- Description:	Added in the columns: CountofEnrolsWithIYPoints, CountofEnrolsWithMissingIYPoints, CountofEnrolsWithEnrolmentPoints, CountofEnrolsWithMissingEnrolPoints
-- =============================================

--DROP TABLE  #TempIDs
--DECLARE @AcademicYearID VARCHAR(5)
--DECLARE @Grouping1 VARCHAR(max)
--DECLARE @Grouping2 VARCHAR(max)
--DECLARE @Grouping3 VARCHAR(max)
--DECLARE @Grouping4 VARCHAR(max)
--DECLARE @SavedViewID INTEGER
--DECLARE @Grouping1Filter VARCHAR(max) = NULL
--SELECT @Grouping1 = 'CourseCode'
--SELECT @Grouping2 = ''''''
--SELECT @Grouping3 = ''''''
--SELECT @Grouping4 = ''''''
--SELECT @AcademicYearID = '19/20'
--SELECT @SavedViewID = 22
--SELECT @Grouping1Filter = 'Q50DCBY2-23/24'

DECLARE @Filter AS VARCHAR(max)

-- First of All, build the Filter String - if one has been passed through
-- ********************************
	SELECT @Filter = ''
	DECLARE @Expression VARCHAR(max)

	DECLARE cur CURSOR FOR
	--Select all the visible Academic Years
	SELECT  
		System_SavedView_Filter.Expression 
	FROM    
		System_SavedView_Filter
	WHERE 
		System_SavedView_Filter.SavedViewID = @SavedViewID

				--Loop through the Academic Years and populate EstActVA
				OPEN cur
				FETCH NEXT FROM cur INTO @Expression

				WHILE @@FETCH_STATUS = 0 
					BEGIN

						SELECT @Filter = @Filter + 	' AND ' + REPLACE(@Expression,'*','%') --REPLACE(@Expression,'''','''''''')
				
					FETCH NEXT FROM cur INTO @Expression

					END

				CLOSE cur
				DEALLOCATE cur

-- ********************************
--Now we have the filter string, carry on...

DECLARE @Grouping1FilterString AS VARCHAR(max)
SELECT @Grouping1FilterString = ''

--If the @GroupingFilter is not NULL and we have a Grouping1, then act normally
IF (@Grouping1 <> 'TeachingGroupCode') AND (@Grouping1Filter IS NOT NULL)
BEGIN
	SELECT @Grouping1FilterString = CASE WHEN @Grouping1Filter IS NULL THEN '' ELSE ' AND ISNULL(' + CAST(@Grouping1 AS VARCHAR(100)) + ','''') = ''' + @Grouping1Filter + '''' END
END

------If the @Grouping1 is 'TeachingGroupCode' and we can get records directly from EstActVA, then act normally
----IF @Grouping1 = 'TeachingGroupCode' AND ( (SELECT COUNT(ID) FROM EstActVA WHERE TeachingGroupCode = @Grouping1Filter AND AcademicYearID = @AcademicYearID) > 0 OR @Grouping1Filter IS NULL)
----BEGIN
----	SELECT @Grouping1FilterString = CASE WHEN @Grouping1Filter IS NULL THEN '' ELSE ' AND ISNULL(' + CAST(@Grouping1 AS VARCHAR(100)) + ','''') = ''' + @Grouping1Filter + '''' END
----END

DECLARE @Grouping1INNERJOINString AS VARCHAR(max)
SELECT @Grouping1INNERJOINString = ''

--If the @Grouping1 is 'TeachingGroupCode' and records aren't retrieved direct from EstActVA, it means we have a manual group
--and we have to go looking for the relevant records...
IF @Grouping1 = 'TeachingGroupCode' /*AND (SELECT COUNT(ID) FROM EstActVA WHERE TeachingGroupCode = @Grouping1Filter AND AcademicYearID = @AcademicYearID) = 0*/ AND @Grouping1Filter IS NOT NULL
BEGIN
	--Load up a #Temp table with IDs that we need for enrolments in a Manual Group.  This uses the OriginalCourseID to get the correct EstActVA.IDs
			SELECT 
			EstActVA.ID
			INTO #TempIDs
			FROM
				(
					SELECT 
					OGP_Enrolment.StudentRef,
					Course.CourseCode,
					OGP_Group.AcademicYearID AS AcademicYearID
					FROM
					OGP_Enrolment
					INNER JOIN OGP_GroupEnrolment
					ON OGP_Enrolment.ID = OGP_GroupEnrolment.OGP_EnrolmentID
					INNER JOIN OGP_Group
					ON OGP_Group.ID = OGP_GroupEnrolment.OGP_GroupID
					INNER JOIN Course
					ON Course.ID = OGP_Enrolment.OriginalCourseID
					WHERE OGP_Enrolment.RecordStatus <> 'Obsolete'
					AND OGP_GroupEnrolment.RecordStatus <> 'Obsolete'
					AND OGP_Group.RecordStatus <> 'Obsolete'
					AND OGP_Group.OGP_GroupCode = @Grouping1Filter AND OGP_Group.OGP_TypeCode = 'VA' AND OGP_Group.AcademicYearID = @AcademicYearID
				) t
			INNER JOIN EstActVA
			ON EstActVA.StudentRef = t.StudentRef
			AND EstActVA.CourseCode = t.CourseCode
			AND EstActVA.AcademicYearID = t.AcademicYearID
	--Form an "INNER JOIN #Temp ON EstActVA.ID = #Temp.ID" statement for manual groups - we'll use this later when populating #Temp
	SELECT @Grouping1INNERJOINString = ' INNER JOIN #TempIDs ON EstActVA.ID = #TempIDs.ID ' 

END


SELECT @Filter = ISNULL(@Filter,'') + @Grouping1FilterString


--SELECT @Filter
--SELECT @Grouping1INNERJOINString


DECLARE @SQL1 VARCHAR(Max)
DECLARE @SQL2 VARCHAR(Max)


SELECT @SQL1 = ''
SELECT @SQL2 = ''
--SELECT @Filter = 'AND Size = 1.0 AND Cohort = ''A Level'''


--Calculate the AcademicYear from where we are going to pull the National Progress Data
DECLARE @AcademicYearNo INTEGER
SELECT @AcademicYearNo = AcademicYearNo FROM AcademicYear WHERE AcademicYearID = @AcademicYearID

DECLARE @AcademicYearID_NationalProgress VARCHAR(5)
SELECT @AcademicYearID_NationalProgress = (
											SELECT MAX(NationalProgress.AcademicYearID) 
											FROM NationalProgress 
											INNER JOIN AcademicYear 
												ON NationalProgress.AcademicYearID = AcademicYear.AcademicYearID
											WHERE AcademicYear.AcademicYearNo <= @AcademicYearNo
										)

--IF OBJECT_ID('tempdb..#tStudent','U') IS NOT NULL DROP TABLE #tStudent
--IF OBJECT_ID('tempdb..#Temp','U') IS NOT NULL DROP TABLE #Temp
--IF OBJECT_ID('tempdb..#TempFinal','U') IS NOT NULL DROP TABLE #TempFinal

/*
--No longer needed 23/11/2023 because We do UniqueStudentCount using a partition instead
CREATE TABLE #tStudent(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	AcademicYearID [varchar](5) NULL,
	VA_Type [varchar](50) NULL,
	Cohort [varchar](120) NULL,
	CA_QualTypeID [INT] NULL,
	QualificationID [INT] NULL,
	OfqualGradingScheme [varchar](100) NULL,
	Grouping1 [varchar](1000) NULL,
	Grouping2 [varchar](1000) NULL,
	Grouping3 [varchar](1000) NULL,
	Grouping4 [varchar](1000) NULL,
	CountOfStudent [int] NULL
	) 
*/
CREATE TABLE #Temp(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	AcademicYearID [varchar](5) NULL,
	Grouping1 [varchar](1000) NULL,
	Grouping2 [varchar](1000) NULL,
	Grouping3 [varchar](1000) NULL,
	Grouping4 [varchar](1000) NULL,
	CountOfEnrol [int] NULL,
	UniqueStudentCount [int] NULL,
	CtQOEPts_Coll [int] NULL,
	CtQOEPts_GCSE_Coll [int] NULL,
	--CtQOEPts_Fin [int] NULL,
	--CtQOEPts_GCSE_Fin [int] NULL,
	--[EstPoints_AcYrmin3] [decimal](19,14) NULL,
	--[EstPoints_AcYrMin2] [decimal](19,14) NULL,
	--[EstPoints_AcYrMin1] [decimal](19,14) NULL,
	--[VAScore_vs_Orig] [decimal](19,14) NULL,		--
	--[VAScore_vs_Adj] [decimal](19,14) NULL,			--
	--[VAScore_L3VAStu] [decimal](19,14) NULL,		--
	--[EstPoints_L3VAStu] [decimal](19,14) NULL,
	--[ActPoints_L3VAStu] [decimal](19,2) NULL,
	--EnrolmentGradeVAScore_vs_Orig [decimal](19,14) NULL,		--
	--EnrolmentGradeVAScore_vs_Adj [decimal](19,14) NULL,			--
	[QOEPoints_College] [decimal](19,14) NULL,
	[QOEPoints_GCSE_College] [decimal](19,14) NULL,
	--[QOEPoints_Final] [decimal](19,14) NULL,
	--[QOEPoints_GCSE_Final] [decimal](19,14) NULL,
	Size [decimal](19,2) NULL,
	VA_Type [varchar](50) NULL,
	Cohort [varchar](120) NULL,
	IYMostRecentPoints [decimal](19,2) NULL,
	--IYMostRecent_vs_Orig [decimal](19,14) NULL,
	--IYMostRecent_vs_Adj [decimal](19,14) NULL,
	IYMostRecent_vs_RecentTarget [decimal](19,14) NULL,		--
	Point1_Points [decimal](19,2) NULL,
	Point2_Points [decimal](19,2) NULL,
	Point3_Points [decimal](19,2) NULL,
	Point4_Points [decimal](19,2) NULL,
	Point5_Points [decimal](19,2) NULL,
	Point6_Points [decimal](19,2) NULL,
	Point7_Points [decimal](19,2) NULL,
	Point8_Points [decimal](19,2) NULL,
	Point9_Points [decimal](19,2) NULL,
	Point10_Points [decimal](19,2) NULL,
	Point11_Points [decimal](19,2) NULL,
	Point12_Points [decimal](19,2) NULL,
	Point1_vs_MostRecent [decimal](19,14) NULL,
	Point2_vs_MostRecent [decimal](19,14) NULL,
	Point3_vs_MostRecent [decimal](19,14) NULL,
	Point4_vs_MostRecent [decimal](19,14) NULL,
	Point5_vs_MostRecent [decimal](19,14) NULL,
	Point6_vs_MostRecent [decimal](19,14) NULL,
	Point7_vs_MostRecent [decimal](19,14) NULL,
	Point8_vs_MostRecent [decimal](19,14) NULL,
	Point9_vs_MostRecent [decimal](19,14) NULL,
	Point10_vs_MostRecent [decimal](19,14) NULL,
	Point11_vs_MostRecent [decimal](19,14) NULL,
	Point12_vs_MostRecent [decimal](19,14) NULL,
	MostRecentCollegeEstPoints [decimal](19,14) NULL,
	VAScore_vs_MostRecent [decimal](19,14) NULL,			--
	--EnrolmentGradeVAScore_vs_AcYr [decimal](19,14) NULL,	--
	EnrolmentGradeVAScore_vs_MostRecent [decimal](19,14) NULL,		--
	EnrolmentPoints [decimal](19,2) NULL,
	QualificationID [int] NULL,
	CA_QualTypeID [int] NULL,
	OfqualGradingScheme [varchar](255) NULL,
	IYMostRecentGradeNo [int] NULL,
	IYPoint1GradeNo [int] NULL,
	IYPoint2GradeNo [int] NULL,
	IYPoint3GradeNo [int] NULL,
	IYPoint4GradeNo [int] NULL,
	IYPoint5GradeNo [int] NULL,
	IYPoint6GradeNo [int] NULL,
	IYPoint7GradeNo [int] NULL,
	IYPoint8GradeNo [int] NULL,
	IYPoint9GradeNo [int] NULL,
	IYPoint10GradeNo [int] NULL,
	IYPoint11GradeNo [int] NULL,
	IYPoint12GradeNo [int] NULL,
	MostRecentCollegeGradeNo [int] NULL,
	--[EstPoints_AcYr] [decimal](19,14) NULL
	CtEnrIYPts [int] NULL,
	CtEnrEnrolmentPts [int] NULL
	) 
	--failed idea - joining on varchars seems to be quicker than ints!
	--Get the Groupings into tables with ID Numbers - we only need to join on the IDs (INT) rather than the varchars (should be quicker)
	--CREATE TABLE #TempGrouping1(
	--[ID] [int] NOT NULL,
	--Grouping1 [varchar](1000) NULL,
	--)
	--CREATE TABLE #TempGrouping2(
	--[ID] [int] NOT NULL,
	--Grouping2 [varchar](1000) NULL,
	--)
	--CREATE TABLE #TempGrouping3(
	--[ID] [int] NOT NULL,
	--Grouping3 [varchar](1000) NULL,
	--)
	--CREATE TABLE #TempGrouping4(
	--[ID] [int] NOT NULL,
	--Grouping4 [varchar](1000) NULL,
	--)
	--SELECT @SQL1 = 
	--'SELECT ROW_NUMBER() OVER (ORDER BY Grouping1)  AS ID, T.Grouping1 FROM (SELECT DISTINCT ISNULL(' + CAST(@Grouping1 AS VARCHAR(100)) + ','''') AS Grouping1 FROM EstActVA WHERE AcademicYearID = ''' + @AcademicYearID + '''' + @Filter + ') T'
	--INSERT INTO #TempGrouping1 exec (@SQL1)
	--SELECT @SQL1 = 
	--'SELECT ROW_NUMBER() OVER (ORDER BY Grouping2)  AS ID, T.Grouping2 FROM (SELECT DISTINCT ISNULL(' + CAST(@Grouping2 AS VARCHAR(100)) + ','''') AS Grouping2 FROM EstActVA WHERE AcademicYearID = ''' + @AcademicYearID + '''' + @Filter + ') T'
	--INSERT INTO #TempGrouping2 exec (@SQL1)
	--SELECT @SQL1 = 
	--'SELECT ROW_NUMBER() OVER (ORDER BY Grouping3)  AS ID, T.Grouping3 FROM (SELECT DISTINCT ISNULL(' + CAST(@Grouping3 AS VARCHAR(100)) + ','''') AS Grouping3 FROM EstActVA WHERE AcademicYearID = ''' + @AcademicYearID + '''' + @Filter + ') T'
	--INSERT INTO #TempGrouping3 exec (@SQL1)
	--SELECT @SQL1 = 
	--'SELECT ROW_NUMBER() OVER (ORDER BY Grouping4)  AS ID, T.Grouping4 FROM (SELECT DISTINCT ISNULL(' + CAST(@Grouping4 AS VARCHAR(100)) + ','''') AS Grouping4 FROM EstActVA WHERE AcademicYearID = ''' + @AcademicYearID + '''' + @Filter + ') T'
	--INSERT INTO #TempGrouping4 exec (@SQL1)


SELECT @SQL1 = 
	'
	SELECT 
	AcademicYearID,
	ISNULL( CAST('+@Grouping1+' AS VARCHAR(100)),'''') AS Grouping1,
	ISNULL( CAST('+@Grouping2+' AS VARCHAR(100)),'''') AS Grouping2,
	ISNULL( CAST('+@Grouping3+' AS VARCHAR(100)),'''') AS Grouping3,
	ISNULL( CAST('+@Grouping4+' AS VARCHAR(100)),'''') AS Grouping4,
	--(SELECT ID FROM #TempGrouping1 WHERE Grouping1 = ISNULL(' + CAST(@Grouping1 AS VARCHAR(100)) + ','''')) AS Grouping1Num,
	--(SELECT ID FROM #TempGrouping2 WHERE Grouping2 = ISNULL(' + CAST(@Grouping2 AS VARCHAR(100)) + ','''')) AS Grouping2Num,
	--(SELECT ID FROM #TempGrouping3 WHERE Grouping3 = ISNULL(' + CAST(@Grouping3 AS VARCHAR(100)) + ','''')) AS Grouping3Num,
	--(SELECT ID FROM #TempGrouping4 WHERE Grouping4 = ISNULL(' + CAST(@Grouping4 AS VARCHAR(100)) + ','''')) AS Grouping4Num,
	1 As CountOfEnrol,--Count(ID) As CountOfEnrol,
	CASE ROW_NUMBER() OVER (PARTITION BY AcademicYearID, StudentRef, VA_Type, Cohort, 
	
			CASE VA_Type 
				WHEN ''CA'' THEN (SELECT CA_QualType.QualTypeDescription FROM CA_QualType WHERE CA_QualType.ID = CA_QualTypeID)
				WHEN ''Pass Only'' THEN  ''Pass Only''
				WHEN ''Ofqual Graded'' THEN OfqualGradingScheme
				WHEN ''NoGrade'' THEN ''NoGrade'' 
				WHEN ''L3VA'' THEN (SELECT QualificationLookup.QualificationDescription FROM QualificationLookup WHERE QualificationLookup.ID = QualificationID)
				ELSE NULL
			END,

	ISNULL( CAST('+@Grouping1+' AS VARCHAR(100)),''''), ISNULL( CAST('+@Grouping2+' AS VARCHAR(100)),''''), ISNULL( CAST('+@Grouping3+' AS VARCHAR(100)),''''), ISNULL( CAST('+@Grouping4+' AS VARCHAR(100)),'''') ORDER BY AcademicYearID, StudentRef DESC) WHEN 1 THEN 1 ELSE 0 END AS UniqueStudentCount,
	(CASE WHEN QOEPoints_College IS NULL THEN 0 ELSE 1 END) AS CtQOEPts_Coll,
	(CASE WHEN QOEPoints_GCSE_College IS NULL THEN 0 ELSE 1 END) AS CtQOEPts_GCSE_Coll,
	--(CASE WHEN QOEPoints_Final IS NULL THEN 0 ELSE 1 END) AS CtQOEPts_Fin,
	--(CASE WHEN QOEPoints_GCSE_Final IS NULL THEN 0 ELSE 1 END) AS CtQOEPts_GCSE_Fin,
	--[EstPoints_AcYrmin3],
	--[EstPoints_AcYrMin2],
	--[EstPoints_AcYrMin1],
	--[VAScore_vs_Orig],
	--[VAScore_vs_Adj],
	--[VAScore_L3VAStu],

	--[EstPoints_L3VAStu],
	--[ActPoints_L3VAStu],

	--EnrolmentGradeVAScore_vs_Orig,
	--EnrolmentGradeVAScore_vs_Adj,
	[QOEPoints_College],
	[QOEPoints_GCSE_College],
	--[QOEPoints_Final],
	--[QOEPoints_GCSE_Final],
	(CASE WHEN Cohort IN (''Pass Only'', ''Ofqual Graded'', ''NoGrade'') THEN 1 ELSE Size END) AS Size,
	VA_Type,
	Cohort,
	IYMostRecentPoints,
	--IYMostRecent_vs_Orig,	
	--IYMostRecent_vs_Adj,
	IYMostRecent_vs_RecentTarget,
	Point1_Points,
	Point2_Points,
	Point3_Points,
	Point4_Points,
	Point5_Points,
	Point6_Points,
	Point7_Points,
	Point8_Points,
	Point9_Points,
	Point10_Points,
	Point11_Points,
	Point12_Points,
	Point1_vs_MostRecent,
	Point2_vs_MostRecent,
	Point3_vs_MostRecent,
	Point4_vs_MostRecent,
	Point5_vs_MostRecent,
	Point6_vs_MostRecent,
	Point7_vs_MostRecent,
	Point8_vs_MostRecent,
	Point9_vs_MostRecent,
	Point10_vs_MostRecent,
	Point11_vs_MostRecent,
	Point12_vs_MostRecent,
	MostRecentCollegeEstPoints,
	VAScore_vs_MostRecent,
	--EnrolmentGradeVAScore_vs_AcYr,
	EnrolmentGradeVAScore_vs_MostRecent,
	EnrolmentPoints,
	CASE VA_Type WHEN ''L3VA'' THEN QualificationID ELSE NULL END AS QualificationID,
	CASE VA_Type WHEN ''CA'' THEN CA_QualTypeID ELSE NULL END AS CA_QualTypeID,
	CASE VA_Type WHEN ''Ofqual Graded'' THEN OfqualGradingScheme ELSE '''' END AS OfqualGradingScheme,

	CASE WHEN IYMostRecentPoints IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = ''L3VA'' THEN 
			dbo.fn_CalculateGradeNo(QualificationID, IYMostRecentPoints)		
		WHEN VA_Type = ''CA'' THEN 
			dbo.fn_CA_CalculateGradeNo(CA_QualTypeID, IYMostRecentPoints, Cohort)
		WHEN VA_Type = ''Pass Only'' THEN 
			dbo.fn_CalculateGradeNo_PassOnly(IYMostRecentPoints)
		WHEN VA_Type = ''Ofqual Graded'' THEN
			dbo.fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, IYMostRecentPoints)
		ELSE
			dbo.fn_CalculateGradeNo_NoGrade(IYMostRecentPoints)
		END 
	END
	AS IYMostRecentGradeNo,

	CASE WHEN Point1_Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = ''L3VA'' THEN 
			dbo.fn_CalculateGradeNo(QualificationID, Point1_Points)
		WHEN VA_Type = ''Pass Only'' THEN 
			dbo.fn_CalculateGradeNo_PassOnly(Point1_Points)
		WHEN VA_Type = ''CA'' THEN
			dbo.fn_CA_CalculateGradeNo(CA_QualTypeID, Point1_Points, Cohort)
		WHEN VA_Type = ''Ofqual Graded'' THEN
			dbo.fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, Point1_Points)
		ELSE
			dbo.fn_CalculateGradeNo_NoGrade(Point1_Points)
		END 
	END
	AS IYPoint1GradeNo,

	CASE WHEN Point2_Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = ''L3VA'' THEN 
			dbo.fn_CalculateGradeNo(QualificationID, Point2_Points)
		WHEN VA_Type = ''Pass Only'' THEN 
			dbo.fn_CalculateGradeNo_PassOnly(Point2_Points)
		WHEN VA_Type = ''CA'' THEN
			dbo.fn_CA_CalculateGradeNo(CA_QualTypeID, Point2_Points, Cohort)
		WHEN VA_Type = ''Ofqual Graded'' THEN
			dbo.fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, Point2_Points)
		ELSE
			dbo.fn_CalculateGradeNo_NoGrade(Point2_Points)
		END 
	END
	AS IYPoint2GradeNo,

	CASE WHEN Point3_Points  IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = ''L3VA'' THEN 
			dbo.fn_CalculateGradeNo(QualificationID, Point3_Points)
		WHEN VA_Type = ''Pass Only'' THEN 
			dbo.fn_CalculateGradeNo_PassOnly(Point3_Points)
		WHEN VA_Type = ''CA'' THEN
			dbo.fn_CA_CalculateGradeNo(CA_QualTypeID, Point3_Points, Cohort)
		WHEN VA_Type = ''Ofqual Graded'' THEN
			dbo.fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, Point3_Points)
		ELSE
			dbo.fn_CalculateGradeNo_NoGrade(Point3_Points)
		END 
	END
	AS IYPoint3GradeNo,

	CASE WHEN Point4_Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = ''L3VA'' THEN 
			dbo.fn_CalculateGradeNo(QualificationID, Point4_Points)
		WHEN VA_Type = ''Pass Only'' THEN 
			dbo.fn_CalculateGradeNo_PassOnly(Point4_Points)
		WHEN VA_Type = ''CA'' THEN
			dbo.fn_CA_CalculateGradeNo(CA_QualTypeID, Point4_Points, Cohort)
		WHEN VA_Type = ''Ofqual Graded'' THEN
			dbo.fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, Point4_Points)
		ELSE
			dbo.fn_CalculateGradeNo_NoGrade(Point4_Points)
		END 
	END
	AS IYPoint4GradeNo,

	CASE WHEN Point5_Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = ''L3VA'' THEN 
			dbo.fn_CalculateGradeNo(QualificationID, Point5_Points)
		WHEN VA_Type = ''Pass Only'' THEN 
			dbo.fn_CalculateGradeNo_PassOnly(Point5_Points)
		WHEN VA_Type = ''CA'' THEN
			dbo.fn_CA_CalculateGradeNo(CA_QualTypeID, Point5_Points, Cohort)
		WHEN VA_Type = ''Ofqual Graded'' THEN
			dbo.fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, Point5_Points)
		ELSE
			dbo.fn_CalculateGradeNo_NoGrade(Point5_Points)
		END 
	END
	AS IYPoint5GradeNo,

	CASE WHEN Point6_Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = ''L3VA'' THEN 
			dbo.fn_CalculateGradeNo(QualificationID, Point6_Points)
		WHEN VA_Type = ''Pass Only'' THEN 
			dbo.fn_CalculateGradeNo_PassOnly(Point6_Points)
		WHEN VA_Type = ''CA'' THEN
			dbo.fn_CA_CalculateGradeNo(CA_QualTypeID, Point6_Points, Cohort)
		WHEN VA_Type = ''Ofqual Graded'' THEN
			dbo.fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, Point6_Points)
		ELSE
			dbo.fn_CalculateGradeNo_NoGrade(Point6_Points)
		END 
	END
	AS IYPoint6GradeNo,

	CASE WHEN Point7_Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = ''L3VA'' THEN 
			dbo.fn_CalculateGradeNo(QualificationID, Point7_Points)
		WHEN VA_Type = ''Pass Only'' THEN 
			dbo.fn_CalculateGradeNo_PassOnly(Point7_Points)
		WHEN VA_Type = ''CA'' THEN
			dbo.fn_CA_CalculateGradeNo(CA_QualTypeID, Point7_Points, Cohort)
		WHEN VA_Type = ''Ofqual Graded'' THEN
			dbo.fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, Point7_Points)
		ELSE
			dbo.fn_CalculateGradeNo_NoGrade(Point7_Points)
		END 
	END
	AS IYPoint7GradeNo,

	CASE WHEN Point8_Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = ''L3VA'' THEN 
			dbo.fn_CalculateGradeNo(QualificationID, Point8_Points)
		WHEN VA_Type = ''Pass Only'' THEN 
			dbo.fn_CalculateGradeNo_PassOnly(Point8_Points)
		WHEN VA_Type = ''CA'' THEN
			dbo.fn_CA_CalculateGradeNo(CA_QualTypeID, Point8_Points, Cohort)
		WHEN VA_Type = ''Ofqual Graded'' THEN
			dbo.fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, Point8_Points)
		ELSE
			dbo.fn_CalculateGradeNo_NoGrade(Point8_Points)
		END
	END
	AS IYPoint8GradeNo,

	CASE WHEN Point9_Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = ''L3VA'' THEN 
			dbo.fn_CalculateGradeNo(QualificationID, Point9_Points)
		WHEN VA_Type = ''Pass Only'' THEN 
			dbo.fn_CalculateGradeNo_PassOnly(Point9_Points)
		WHEN VA_Type = ''CA'' THEN
			dbo.fn_CA_CalculateGradeNo(CA_QualTypeID, Point9_Points, Cohort)
		WHEN VA_Type = ''Ofqual Graded'' THEN
			dbo.fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, Point9_Points)
		ELSE
			dbo.fn_CalculateGradeNo_NoGrade(Point9_Points)
		END
	END
	AS IYPoint9GradeNo,

	CASE WHEN Point10_Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = ''L3VA'' THEN 
			dbo.fn_CalculateGradeNo(QualificationID, Point10_Points)
		WHEN VA_Type = ''Pass Only'' THEN 
			dbo.fn_CalculateGradeNo_PassOnly(Point10_Points)
		WHEN VA_Type = ''CA'' THEN
			dbo.fn_CA_CalculateGradeNo(CA_QualTypeID, Point10_Points, Cohort)
		WHEN VA_Type = ''Ofqual Graded'' THEN
			dbo.fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, Point10_Points)
		ELSE
			dbo.fn_CalculateGradeNo_NoGrade(Point10_Points)
		END
	END
	AS IYPoint10GradeNo,

	CASE WHEN Point11_Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = ''L3VA'' THEN 
			dbo.fn_CalculateGradeNo(QualificationID, Point11_Points)
		WHEN VA_Type = ''Pass Only'' THEN 
			dbo.fn_CalculateGradeNo_PassOnly(Point11_Points)
		WHEN VA_Type = ''CA'' THEN
			dbo.fn_CA_CalculateGradeNo(CA_QualTypeID, Point11_Points, Cohort)
		WHEN VA_Type = ''Ofqual Graded'' THEN
			dbo.fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, Point11_Points)
		ELSE
			dbo.fn_CalculateGradeNo_NoGrade(Point11_Points)
		END
	END
	AS IYPoint11GradeNo,

	CASE WHEN Point12_Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = ''L3VA'' THEN 
			dbo.fn_CalculateGradeNo(QualificationID, Point12_Points)
		WHEN VA_Type = ''Pass Only'' THEN 
			dbo.fn_CalculateGradeNo_PassOnly(Point12_Points)
		WHEN VA_Type = ''CA'' THEN
			dbo.fn_CA_CalculateGradeNo(CA_QualTypeID, Point12_Points, Cohort)
		WHEN VA_Type = ''Ofqual Graded'' THEN
			dbo.fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, Point12_Points)
		ELSE
			dbo.fn_CalculateGradeNo_NoGrade(Point12_Points)
		END
	END
	AS IYPoint12GradeNo,
	
	CASE WHEN MostRecentCollegeEstPoints IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = ''L3VA'' THEN 
			dbo.fn_CalculateGradeNo(QualificationID, MostRecentCollegeEstPoints)
		WHEN VA_Type = ''Pass Only'' THEN 
			dbo.fn_CalculateGradeNo_PassOnly(MostRecentCollegeEstPoints)
		WHEN VA_Type = ''CA'' THEN
			dbo.fn_CA_CalculateGradeNo(CA_QualTypeID, MostRecentCollegeEstPoints, Cohort)
		WHEN VA_Type = ''Ofqual Graded'' THEN
			dbo.fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, MostRecentCollegeEstPoints)
		ELSE
			dbo.fn_CalculateGradeNo_NoGrade(MostRecentCollegeEstPoints)
		END 
	END 
	AS MostRecentCollegeGradeNo,
	(CASE WHEN IYMostRecentPoints IS NULL THEN 0 ELSE 1 END) AS CtEnrIYPts,
	(CASE WHEN EnrolmentPoints IS NULL THEN 0 ELSE 1 END) AS CtEnrEnrolmentPts
	--EstPoints_AcYr

	FROM
	EstActVA ' + @Grouping1INNERJOINString + '
	WHERE AcademicYearID = ''' + @AcademicYearID + '''
	' + @Filter + '

	'
	--print @SQL1
	INSERT INTO #Temp exec (@SQL1)

	--select * from #Temp where Grouping1 = 'R209'
	--*****************************************************

/*
--No longer needed 23/11/2023 because We do UniqueStudentCount using a partition instead

IF LEN(REPLACE(@Grouping1,'''','')) > 0 AND LEN(REPLACE(@Grouping2,'''','')) < 1 
BEGIN

	SELECT @SQL1 = 
	'
	SELECT 
	AcademicYearID,
	VA_Type,
	Cohort,
	CA_QualTypeID,
	QualificationID,
	OfqualGradingScheme,
	COALESCE(CAST(' + @Grouping1 + ' AS VARCHAR(100)),'''') AS Grouping1,
	'''' AS Grouping2Num,
	'''' AS Grouping3Num,
	'''' AS Grouping4Num,
	Count(StudentRef) As CountOfStudent
	FROM
			(
			SELECT DISTINCT
			AcademicYearID,
			VA_Type,
			Cohort,
			CA_QualTypeID,
			QualificationID,
			ISNULL(OfqualGradingScheme,'''') AS OfqualGradingScheme,
			StudentRef,
			ISNULL(CAST(' + @Grouping1 + ' AS VARCHAR(100)),'''') AS ' + @Grouping1 + '
			FROM EstActVA ' + @Grouping1INNERJOINString + '
			WHERE 1=1 AND AcademicYearID = ''' + @AcademicYearID + '''' + @Filter + '
			) t
	GROUP BY AcademicYearID, VA_Type, Cohort, CA_QualTypeID, QualificationID, OfqualGradingScheme, ' + @Grouping1 + '
	'
END 

IF LEN(REPLACE(@Grouping1,'''','')) > 0 AND LEN(REPLACE(@Grouping2,'''','')) > 0 AND LEN(REPLACE(@Grouping3,'''','')) < 1 
BEGIN
--2
	SELECT @SQL1 = 
	'
	SELECT 
	AcademicYearID,
	VA_Type,
	Cohort,
	CA_QualTypeID,
	QualificationID,
	OfqualGradingScheme,
	COALESCE(CAST(' + @Grouping1 + ' AS VARCHAR(100)),'''') AS Grouping1,
	COALESCE(CAST(' + @Grouping2 + ' AS VARCHAR(100)),'''') AS Grouping2,
	'''' AS Grouping3Num,
	'''' AS Grouping4Num,
	Count(StudentRef) As CountOfStudent
	FROM
			(
			SELECT DISTINCT
			AcademicYearID,
			VA_Type,
			Cohort,
			CA_QualTypeID,
			QualificationID,
			ISNULL(OfqualGradingScheme,'''') AS OfqualGradingScheme,
			StudentRef,
			ISNULL(CAST(' + @Grouping1 + ' AS VARCHAR(100)),'''') AS ' + @Grouping1 + ',
			ISNULL(CAST(' + @Grouping2 + ' AS VARCHAR(100)),'''') AS ' + @Grouping2 + '
			FROM EstActVA  ' + @Grouping1INNERJOINString + '
			WHERE 1=1 AND AcademicYearID = ''' + @AcademicYearID + '''' + @Filter + '
			) t
	GROUP BY AcademicYearID,VA_Type, Cohort, CA_QualTypeID, QualificationID, OfqualGradingScheme, ' + @Grouping1 + ' , ' + @Grouping2 + '
	'
END

IF LEN(REPLACE(@Grouping1,'''','')) > 0 AND LEN(REPLACE(@Grouping2,'''','')) > 0 AND LEN(REPLACE(@Grouping3,'''','')) > 0 AND LEN(REPLACE(@Grouping4,'''','')) < 1 
BEGIN
--3
	SELECT @SQL1 = 
	'
	SELECT 
	AcademicYearID,
	VA_Type,
	Cohort,
	CA_QualTypeID,
	QualificationID,
	OfqualGradingScheme,
	COALESCE(CAST(' + @Grouping1 + ' AS VARCHAR(100)),'''') AS Grouping1,
	COALESCE(CAST(' + @Grouping2 + ' AS VARCHAR(100)),'''') AS Grouping2,
	COALESCE(CAST(' + @Grouping3 + ' AS VARCHAR(100)),'''') AS Grouping3,
	NULL AS Grouping4Num,
	Count(StudentRef) As CountOfStudent
	FROM
			(
			SELECT DISTINCT
			AcademicYearID,
			VA_Type,
			Cohort,
			CA_QualTypeID,
			QualificationID,
			ISNULL(OfqualGradingScheme,'''') AS OfqualGradingScheme,
			StudentRef,
			ISNULL(CAST(' + @Grouping1 + ' AS VARCHAR(100)),'''') AS ' + @Grouping1 + ',
			ISNULL(CAST(' + @Grouping2 + ' AS VARCHAR(100)),'''') AS ' + @Grouping2 + ',
			ISNULL(CAST(' + @Grouping3 + ' AS VARCHAR(100)),'''') AS ' + @Grouping3 + '
			FROM EstActVA
			WHERE 1=1 AND AcademicYearID = ''' + @AcademicYearID + '''' + @Filter + '
			) t
	GROUP BY AcademicYearID, VA_Type, Cohort, CA_QualTypeID, QualificationID, OfqualGradingScheme, ' + @Grouping1 + ' , ' + @Grouping2 + ' , ' + @Grouping3 + '
	'
END

IF LEN(REPLACE(@Grouping1,'''','')) > 0 AND LEN(REPLACE(@Grouping2,'''','')) > 0 AND LEN(REPLACE(@Grouping3,'''','')) > 0 AND LEN(REPLACE(@Grouping4,'''','')) > 0 
BEGIN
--4
	SELECT @SQL1 = 
	'
	SELECT 
	AcademicYearID,
	VA_Type,
	Cohort,
	CA_QualTypeID,
	QualificationID,
	OfqualGradingScheme,
	COALESCE(CAST(' + @Grouping1 + ' AS VARCHAR(100)),'''') AS Grouping1,
	COALESCE(CAST(' + @Grouping2 + ' AS VARCHAR(100)),'''') AS Grouping2,
	COALESCE(CAST(' + @Grouping3 + ' AS VARCHAR(100)),'''') AS Grouping3,
	COALESCE(CAST(' + @Grouping4 + ' AS VARCHAR(100)),'''') AS Grouping4,
	Count(StudentRef) As CountOfStudent
	FROM
			(
			SELECT DISTINCT
			AcademicYearID,
			VA_Type,
			Cohort,
			CA_QualTypeID,
			QualificationID,
			ISNULL(OfqualGradingScheme,'''') AS OfqualGradingScheme,
			StudentRef,
			ISNULL(CAST(' + @Grouping1 + ' AS VARCHAR(100)),'''') AS ' + @Grouping1 + ',
			ISNULL(CAST(' + @Grouping2 + ' AS VARCHAR(100)),'''') AS ' + @Grouping2 + ',
			ISNULL(CAST(' + @Grouping3 + ' AS VARCHAR(100)),'''') AS ' + @Grouping3 + ',
			ISNULL(CAST(' + @Grouping4 + ' AS VARCHAR(100)),'''') AS ' + @Grouping4 + '
			FROM EstActVA
			WHERE 1=1 AND AcademicYearID = ''' + @AcademicYearID + '''' + @Filter + '
			) t
	GROUP BY AcademicYearID, VA_Type, Cohort, CA_QualTypeID, QualificationID, OfqualGradingScheme, ' + @Grouping1 + ' , ' + @Grouping2 + ' , ' + @Grouping3 + ' , ' + @Grouping4 + '
	'
END

	--print @SQL1
	INSERT INTO #tStudent exec (@SQL1)

--	Select * FROM #tStudent
--	--********************************************
*/

	--NOTE: Size is taken into account for VA calcs
	--Size is not taken into account for Avg Grade/Points

	SELECT 
	CAST(ROW_NUMBER() OVER (ORDER BY AcademicYearID) AS INT) AS ID,
	AcademicYearID,
	VA_Type,
	Cohort,
	CA_QualTypeID,
	QualificationID,
	OfqualGradingScheme,
	LEFT(ISNULL(Grouping1,''), 100) AS Grouping1,
	LEFT(ISNULL(Grouping2,''), 100) AS Grouping2,
	LEFT(ISNULL(Grouping3,''), 100) AS Grouping3,
	LEFT(ISNULL(Grouping4,''), 100) AS Grouping4,
	--#Temp.Grouping1Num,
	--#Temp.Grouping2Num,
	--#Temp.Grouping3Num,
	--#Temp.Grouping4Num,
	Sum(CountOfEnrol) AS CountOfEnrol,

	--The UniqueStudentCount is a 1 or a 0.  The 1 is placed against the first EstActVA record it comes across for a student, 0 against all others.  Therefore the QOE average can be worked out PER STUDENT
	CASE WHEN Sum(CtQOEPts_Coll*UniqueStudentCount) = 0 THEN NULL ELSE CAST(Sum([QOEPoints_College]*UniqueStudentCount) / Sum(CtQOEPts_Coll*UniqueStudentCount) AS Decimal(19,2)) END AS GroupingQOEPoints_College,
	Sum([QOEPoints_College]*UniqueStudentCount) AS GroupingQOEPoints_College_Dividend,
	Sum(CtQOEPts_Coll*UniqueStudentCount) AS GroupingQOEPoints_College_Divisor,

	CASE WHEN Sum(CtQOEPts_GCSE_Coll*UniqueStudentCount) = 0 THEN NULL ELSE CAST(Sum([QOEPoints_GCSE_College]*UniqueStudentCount) / Sum(CtQOEPts_GCSE_Coll*UniqueStudentCount) AS Decimal(19,2)) END AS GroupingQOEPoints_GCSE_College,
	Sum([QOEPoints_GCSE_College]*UniqueStudentCount) AS GroupingQOEPoints_GCSE_College_Dividend,
	Sum(CtQOEPts_GCSE_Coll*UniqueStudentCount) AS GroupingQOEPoints_GCSE_College_Divisor,

	--CASE WHEN Sum(CtQOEPts_Fin) = 0 THEN NULL ELSE CAST(Sum([QOEPoints_Final]) / Sum(CtQOEPts_Fin) AS Decimal(19,2)) END AS GroupingQOEPoints_Final,
	--Sum([QOEPoints_Final]) AS GroupingQOEPoints_Final_Dividend,
	--Sum(CtQOEPts_Fin) AS GroupingQOEPoints_Final_Divisor,

	--CASE WHEN Sum(CtQOEPts_GCSE_Fin) = 0 THEN NULL ELSE CAST(Sum([QOEPoints_GCSE_Final]) / Sum(CtQOEPts_GCSE_Fin) AS Decimal(19,2)) END AS GroupingQOEPoints_GCSE_Final,
	--Sum([QOEPoints_GCSE_Final]) AS GroupingQOEPoints_GCSE_Final_Dividend,
	--Sum(CtQOEPts_GCSE_Fin) AS GroupingQOEPoints_GCSE_Final_Divisor,

	--CASE WHEN Sum(CASE WHEN [EstPoints_AcYrmin3] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
	--	ELSE CAST(Sum([EstPoints_AcYrmin3]) / Sum(CASE WHEN [EstPoints_AcYrmin3] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2))
	--END AS GroupingEstPoints_AcYrMin3,
	--CAST(Sum([EstPoints_AcYrmin3]) AS DECIMAL(19,14)) AS GroupingEstPoints_AcYrMin3_Dividend,
	--Sum(CASE WHEN [EstPoints_AcYrmin3] IS NULL THEN 0 ELSE 1 END) AS GroupingEstPoints_AcYrMin3_Divisor,
	
	--CASE WHEN Sum(CASE WHEN [EstPoints_AcYrmin2] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
	--	ELSE CAST(Sum([EstPoints_AcYrmin2]) / Sum(CASE WHEN [EstPoints_AcYrmin2] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2))
	--END AS GroupingEstPoints_AcYrMin2,
	--CAST(Sum([EstPoints_AcYrmin2]) AS DECIMAL(19,14)) AS GroupingEstPoints_AcYrMin2_Dividend,
	--Sum(CASE WHEN [EstPoints_AcYrmin2] IS NULL THEN 0 ELSE 1 END) AS GroupingEstPoints_AcYrMin2_Divisor,
	
	--CASE WHEN Sum(CASE WHEN [EstPoints_AcYrmin1] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
	--	ELSE CAST(Sum([EstPoints_AcYrmin1]) / Sum(CASE WHEN [EstPoints_AcYrmin1] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2))
	--END AS GroupingEstPoints_AcYrMin1,
	--CAST(Sum([EstPoints_AcYrmin1]) AS DECIMAL(19,14)) AS GroupingEstPoints_AcYrMin1_Dividend,
	--Sum(CASE WHEN [EstPoints_AcYrmin1] IS NULL THEN 0 ELSE 1 END) AS GroupingEstPoints_AcYrMin1_Divisor,

	--CASE WHEN Sum(CASE WHEN VAScore_vs_Orig IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
	--	ELSE CAST(Sum([VAScore_vs_Orig]) / Sum(CASE WHEN VAScore_vs_Orig IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	--	END AS GroupingVAScore_vs_Orig,
	--CAST(Sum([VAScore_vs_Orig]) AS DECIMAL(19,14)) AS GroupingVAScore_vs_Orig_Dividend,
	--Sum(CASE WHEN VAScore_vs_Orig IS NULL THEN 0 ELSE 1 END) AS GroupingVAScore_vs_Orig_Divisor,
	
	--CASE WHEN Sum(CASE WHEN VAScore_vs_Adj IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
	--	ELSE CAST(Sum([VAScore_vs_Adj]) / Sum(CASE WHEN VAScore_vs_Adj IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	--	END AS GroupingVAScore_vs_Adj,
	--CAST(Sum([VAScore_vs_Adj]) AS DECIMAL(19,14)) AS GroupingVAScore_vs_Adj_Dividend,
	--Sum(CASE WHEN VAScore_vs_Adj IS NULL THEN 0 ELSE 1 END) AS GroupingVAScore_vs_Adj_Divisor,
	
	--CASE WHEN Sum(CASE WHEN VAScore_L3VAStu IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
	--	ELSE CAST(Sum([VAScore_L3VAStu]) / Sum(CASE WHEN VAScore_L3VAStu IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) --HERE! --Need to only /Size when the divisor is not null!
	--	END AS GroupingVAScore_L3VAStu,
	--CAST(Sum([VAScore_L3VAStu]) AS DECIMAL(19,14)) AS GroupingVAScore_L3VAStu_Dividend,
	--Sum(CASE WHEN VAScore_L3VAStu IS NULL THEN 0 ELSE 1 END) AS GroupingVAScore_L3VAStu_Divisor,
	--Sum(CASE WHEN VAScore_L3VAStu IS NULL THEN 0 ELSE 1 END) AS GroupingVAScore_L3VAStu_NumEnrols, --Work out the number of enrols that have DfE L3VA

	--CASE WHEN Sum(CASE WHEN EstPoints_L3VAStu IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
	--	ELSE CAST(Sum([EstPoints_L3VAStu]) / Sum(CASE WHEN EstPoints_L3VAStu IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	--	END AS GroupingEstPoints_L3VAStu,
	--CAST(Sum([EstPoints_L3VAStu]) AS DECIMAL(19,14)) AS GroupingEstPoints_L3VAStu_Dividend,
	--Sum(CASE WHEN EstPoints_L3VAStu IS NULL THEN 0 ELSE 1 END) AS GroupingEstPoints_L3VAStu_Divisor,

	--CASE WHEN Sum(CASE WHEN ActPoints_L3VAStu IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
	--	ELSE CAST(Sum([ActPoints_L3VAStu]) / Sum(CASE WHEN ActPoints_L3VAStu IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	--	END AS GroupingActPoints_L3VAStu,
	--CAST(Sum([ActPoints_L3VAStu]) AS DECIMAL(19,14)) AS GroupingActPoints_L3VAStu_Dividend,
	--Sum(CASE WHEN ActPoints_L3VAStu IS NULL THEN 0 ELSE 1 END) AS GroupingActPoints_L3VAStu_Divisor,


	--CASE WHEN Sum(CASE WHEN EnrolmentGradeVAScore_vs_Orig IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
	--	ELSE CAST(Sum([EnrolmentGradeVAScore_vs_Orig]) / Sum(CASE WHEN EnrolmentGradeVAScore_vs_Orig IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	--	END AS GroupingEnrolmentGradeVAScore_vs_Orig,
	--CAST(Sum([EnrolmentGradeVAScore_vs_Orig]) AS DECIMAL(19,14)) AS GroupingEnrolmentGradeVAScore_vs_Orig_Dividend,
	--Sum(CASE WHEN EnrolmentGradeVAScore_vs_Orig IS NULL THEN 0 ELSE 1 END) AS GroupingEnrolmentGradeVAScore_vs_Orig_Divisor,

	--CASE WHEN Sum(CASE WHEN EnrolmentGradeVAScore_vs_Adj IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL 
	--	ELSE CAST(Sum([EnrolmentGradeVAScore_vs_Adj]) / Sum(CASE WHEN EnrolmentGradeVAScore_vs_Adj IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	--	END AS GroupingEnrolmentGradeVAScore_vs_Adj,
	--CAST(Sum([EnrolmentGradeVAScore_vs_Adj]) AS DECIMAL(19,14)) AS GroupingEnrolmentGradeVAScore_vs_Adj_Dividend,
	--Sum(CASE WHEN EnrolmentGradeVAScore_vs_Adj IS NULL THEN 0 ELSE 1 END) AS GroupingEnrolmentGradeVAScore_vs_Adj_Divisor,

	CASE WHEN Sum(CASE WHEN [IYMostRecentPoints] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum([IYMostRecentPoints]) / Sum(CASE WHEN [IYMostRecentPoints] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingIYMostRecentPoints,
	CAST(Sum([IYMostRecentPoints]) AS DECIMAL(19,14)) AS GroupingIYMostRecentPoints_Dividend,
	Sum(CASE WHEN [IYMostRecentPoints] IS NULL THEN 0 ELSE 1 END) AS GroupingIYMostRecentPoints_Divisor,


	--CASE WHEN Sum(CASE WHEN IYMostRecent_vs_Orig IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL 
	--	ELSE CAST(Sum([IYMostRecent_vs_Orig]) / Sum(CASE WHEN IYMostRecent_vs_Orig IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	--	END AS GroupingIYMostRecent_vs_Orig,
	--CAST(Sum([IYMostRecent_vs_Orig]) AS DECIMAL(19,14)) AS GroupingIYMostRecent_vs_Orig_Dividend,
	--Sum(CASE WHEN IYMostRecent_vs_Orig IS NULL THEN 0 ELSE 1 END) AS GroupingIYMostRecent_vs_Orig_Divisor,

	--CASE WHEN Sum(CASE WHEN IYMostRecent_vs_Adj IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL 
	--	ELSE CAST(Sum([IYMostRecent_vs_Adj]) / Sum(CASE WHEN IYMostRecent_vs_Adj IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	--	END AS GroupingIYMostRecent_vs_Adj,
	--CAST(Sum([IYMostRecent_vs_Adj]) AS DECIMAL(19,14)) AS GroupingIYMostRecent_vs_Adj_Dividend,
	--Sum(CASE WHEN IYMostRecent_vs_Adj IS NULL THEN 0 ELSE 1 END) AS GroupingIYMostRecent_vs_Adj_Divisor,

	CASE WHEN Sum(CASE WHEN IYMostRecent_vs_RecentTarget IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL 
		ELSE CAST(Sum([IYMostRecent_vs_RecentTarget]) / Sum(CASE WHEN IYMostRecent_vs_RecentTarget IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
		END AS GroupingIYMostRecent_vs_RecentTarget,
	CAST(Sum([IYMostRecent_vs_RecentTarget]) AS DECIMAL(19,14)) AS GroupingIYMostRecent_vs_RecentTarget_Dividend,
	Sum(CASE WHEN IYMostRecent_vs_RecentTarget IS NULL THEN 0 ELSE 1 END) AS GroupingIYMostRecent_vs_RecentTarget_Divisor,


	CASE WHEN Sum(CASE WHEN [Point1_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum([Point1_Points]) / Sum(CASE WHEN [Point1_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingPoint1Points,
	CAST(Sum([Point1_Points]) AS DECIMAL(19,14)) AS GroupingPoint1Points_Dividend,
	Sum(CASE WHEN [Point1_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingPoint1Points_Divisor,

	CASE WHEN Sum(CASE WHEN [Point2_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum([Point2_Points]) / Sum(CASE WHEN [Point2_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingPoint2Points,
	CAST(Sum([Point2_Points]) AS DECIMAL(19,14)) AS GroupingPoint2Points_Dividend,
	Sum(CASE WHEN [Point2_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingPoint2Points_Divisor,

	CASE WHEN Sum(CASE WHEN [Point3_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum([Point3_Points]) / Sum(CASE WHEN [Point3_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingPoint3Points,
	CAST(Sum([Point3_Points]) AS DECIMAL(19,14)) AS GroupingPoint3Points_Dividend,
	Sum(CASE WHEN [Point3_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingPoint3Points_Divisor,

	CASE WHEN Sum(CASE WHEN [Point4_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum([Point4_Points]) / Sum(CASE WHEN [Point4_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingPoint4Points,
	CAST(Sum([Point4_Points]) AS DECIMAL(19,14)) AS GroupingPoint4Points_Dividend,
	Sum(CASE WHEN [Point4_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingPoint4Points_Divisor,

	CASE WHEN Sum(CASE WHEN [Point5_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum([Point5_Points]) / Sum(CASE WHEN [Point5_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingPoint5Points,
	CAST(Sum([Point5_Points]) AS DECIMAL(19,14)) AS GroupingPoint5Points_Dividend,
	Sum(CASE WHEN [Point5_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingPoint5Points_Divisor,

	CASE WHEN Sum(CASE WHEN [Point6_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum([Point6_Points]) / Sum(CASE WHEN [Point6_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingPoint6Points,
	CAST(Sum([Point6_Points]) AS DECIMAL(19,14)) AS GroupingPoint6Points_Dividend,
	Sum(CASE WHEN [Point6_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingPoint6Points_Divisor,

	CASE WHEN Sum(CASE WHEN [Point7_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum([Point7_Points]) / Sum(CASE WHEN [Point7_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingPoint7Points,
	CAST(Sum([Point7_Points]) AS DECIMAL(19,14)) AS GroupingPoint7Points_Dividend,
	Sum(CASE WHEN [Point7_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingPoint7Points_Divisor,

	CASE WHEN Sum(CASE WHEN [Point8_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum([Point8_Points]) / Sum(CASE WHEN [Point8_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingPoint8Points,
	CAST(Sum([Point8_Points]) AS DECIMAL(19,14)) AS GroupingPoint8Points_Dividend,
	Sum(CASE WHEN [Point8_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingPoint8Points_Divisor,

	CASE WHEN Sum(CASE WHEN [Point9_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum([Point9_Points]) / Sum(CASE WHEN [Point9_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingPoint9Points,
	CAST(Sum([Point9_Points]) AS DECIMAL(19,14)) AS GroupingPoint9Points_Dividend,
	Sum(CASE WHEN [Point9_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingPoint9Points_Divisor,

	CASE WHEN Sum(CASE WHEN [Point10_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum([Point10_Points]) / Sum(CASE WHEN [Point10_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingPoint10Points,
	CAST(Sum([Point10_Points]) AS DECIMAL(19,14)) AS GroupingPoint10Points_Dividend,
	Sum(CASE WHEN [Point10_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingPoint10Points_Divisor,

	CASE WHEN Sum(CASE WHEN [Point11_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum([Point11_Points]) / Sum(CASE WHEN [Point11_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingPoint11Points,
	CAST(Sum([Point11_Points]) AS DECIMAL(19,14)) AS GroupingPoint11Points_Dividend,
	Sum(CASE WHEN [Point11_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingPoint11Points_Divisor,

	CASE WHEN Sum(CASE WHEN [Point12_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum([Point12_Points]) / Sum(CASE WHEN [Point12_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingPoint12Points,
	CAST(Sum([Point12_Points]) AS DECIMAL(19,14)) AS GroupingPoint12Points_Dividend,
	Sum(CASE WHEN [Point12_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingPoint12Points_Divisor,

	CASE WHEN Sum(CASE WHEN Point1_vs_MostRecent IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL 
		ELSE CAST(Sum([Point1_vs_MostRecent]) / Sum(CASE WHEN Point1_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
		END AS GroupingPoint1_vs_RecentTarget,
	CAST(Sum([Point1_vs_MostRecent]) AS DECIMAL(19,14)) AS GroupingPoint1_vs_RecentTarget_Dividend,
	Sum(CASE WHEN Point1_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS GroupingPoint1_vs_RecentTarget_Divisor,

		CASE WHEN Sum(CASE WHEN Point2_vs_MostRecent IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL 
		ELSE CAST(Sum([Point2_vs_MostRecent]) / Sum(CASE WHEN Point2_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
		END AS GroupingPoint2_vs_RecentTarget,
	CAST(Sum([Point2_vs_MostRecent]) AS DECIMAL(19,14)) AS GroupingPoint2_vs_RecentTarget_Dividend,
	Sum(CASE WHEN Point2_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS GroupingPoint2_vs_RecentTarget_Divisor,

		CASE WHEN Sum(CASE WHEN Point3_vs_MostRecent IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL 
		ELSE CAST(Sum([Point3_vs_MostRecent]) / Sum(CASE WHEN Point3_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
		END AS GroupingPoint3_vs_RecentTarget,
	CAST(Sum([Point3_vs_MostRecent]) AS DECIMAL(19,14)) AS GroupingPoint3_vs_RecentTarget_Dividend,
	Sum(CASE WHEN Point3_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS GroupingPoint3_vs_RecentTarget_Divisor,

		CASE WHEN Sum(CASE WHEN Point4_vs_MostRecent IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL 
		ELSE CAST(Sum([Point4_vs_MostRecent]) / Sum(CASE WHEN Point4_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
		END AS GroupingPoint4_vs_RecentTarget,
	CAST(Sum([Point4_vs_MostRecent]) AS DECIMAL(19,14)) AS GroupingPoint4_vs_RecentTarget_Dividend,
	Sum(CASE WHEN Point4_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS GroupingPoint4_vs_RecentTarget_Divisor,


		CASE WHEN Sum(CASE WHEN Point5_vs_MostRecent IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL 
		ELSE CAST(Sum([Point5_vs_MostRecent]) / Sum(CASE WHEN Point5_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
		END AS GroupingPoint5_vs_RecentTarget,
	CAST(Sum([Point5_vs_MostRecent]) AS DECIMAL(19,14)) AS GroupingPoint5_vs_RecentTarget_Dividend,
	Sum(CASE WHEN Point5_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS GroupingPoint5_vs_RecentTarget_Divisor,

		CASE WHEN Sum(CASE WHEN Point6_vs_MostRecent IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL 
		ELSE CAST(Sum([Point6_vs_MostRecent]) / Sum(CASE WHEN Point6_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
		END AS GroupingPoint6_vs_RecentTarget,
	CAST(Sum([Point6_vs_MostRecent]) AS DECIMAL(19,14)) AS GroupingPoint6_vs_RecentTarget_Dividend,
	Sum(CASE WHEN Point6_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS GroupingPoint6_vs_RecentTarget_Divisor,

		CASE WHEN Sum(CASE WHEN Point7_vs_MostRecent IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL 
		ELSE CAST(Sum([Point7_vs_MostRecent]) / Sum(CASE WHEN Point7_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
		END AS GroupingPoint7_vs_RecentTarget,
	CAST(Sum([Point7_vs_MostRecent]) AS DECIMAL(19,14)) AS GroupingPoint7_vs_RecentTarget_Dividend,
	Sum(CASE WHEN Point7_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS GroupingPoint7_vs_RecentTarget_Divisor,

		CASE WHEN Sum(CASE WHEN Point8_vs_MostRecent IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL 
		ELSE CAST(Sum([Point8_vs_MostRecent]) / Sum(CASE WHEN Point8_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
		END AS GroupingPoint8_vs_RecentTarget,
	CAST(Sum([Point8_vs_MostRecent]) AS DECIMAL(19,14)) AS GroupingPoint8_vs_RecentTarget_Dividend,
	Sum(CASE WHEN Point8_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS GroupingPoint8_vs_RecentTarget_Divisor,


		CASE WHEN Sum(CASE WHEN Point9_vs_MostRecent IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL 
		ELSE CAST(Sum([Point9_vs_MostRecent]) / Sum(CASE WHEN Point9_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
		END AS GroupingPoint9_vs_RecentTarget,
	CAST(Sum([Point9_vs_MostRecent]) AS DECIMAL(19,14)) AS GroupingPoint9_vs_RecentTarget_Dividend,
	Sum(CASE WHEN Point9_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS GroupingPoint9_vs_RecentTarget_Divisor,

		CASE WHEN Sum(CASE WHEN Point10_vs_MostRecent IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL 
		ELSE CAST(Sum([Point10_vs_MostRecent]) / Sum(CASE WHEN Point10_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
		END AS GroupingPoint10_vs_RecentTarget,
	CAST(Sum([Point10_vs_MostRecent]) AS DECIMAL(19,14)) AS GroupingPoint10_vs_RecentTarget_Dividend,
	Sum(CASE WHEN Point10_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS GroupingPoint10_vs_RecentTarget_Divisor,

		CASE WHEN Sum(CASE WHEN Point11_vs_MostRecent IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL 
		ELSE CAST(Sum([Point11_vs_MostRecent]) / Sum(CASE WHEN Point11_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
		END AS GroupingPoint11_vs_RecentTarget,
	CAST(Sum([Point11_vs_MostRecent]) AS DECIMAL(19,14)) AS GroupingPoint11_vs_RecentTarget_Dividend,
	Sum(CASE WHEN Point11_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS GroupingPoint11_vs_RecentTarget_Divisor,

		CASE WHEN Sum(CASE WHEN Point12_vs_MostRecent IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL 
		ELSE CAST(Sum([Point12_vs_MostRecent]) / Sum(CASE WHEN Point12_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
		END AS GroupingPoint12_vs_RecentTarget,
	CAST(Sum([Point12_vs_MostRecent]) AS DECIMAL(19,14)) AS GroupingPoint12_vs_RecentTarget_Dividend,
	Sum(CASE WHEN Point12_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS GroupingPoint12_vs_RecentTarget_Divisor,

	--AMG
	CASE WHEN Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum([MostRecentCollegeEstPoints]) / Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingMostRecentCollegeEstPoints,
	CAST(Sum([MostRecentCollegeEstPoints]) AS DECIMAL(19,14)) AS GroupingMostRecentCollegeEstPoints_Dividend,
	Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL THEN 0 ELSE 1 END) AS GroupingMostRecentCollegeEstPoints_Divisor,

	CASE WHEN Sum(CASE WHEN VAScore_vs_MostRecent IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL 
		ELSE CAST(Sum([VAScore_vs_MostRecent]) / Sum(CASE WHEN VAScore_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
		END AS GroupingVAScore_vs_MostRecent,
	CAST(Sum([VAScore_vs_MostRecent]) AS DECIMAL(19,14)) AS GroupingVAScore_vs_MostRecent_Dividend,
	Sum(CASE WHEN VAScore_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS GroupingVAScore_vs_MostRecent_Divisor,

	--CASE WHEN Sum(CASE WHEN EnrolmentGradeVAScore_vs_AcYr IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL 
	--	ELSE CAST(Sum([EnrolmentGradeVAScore_vs_AcYr]) / Sum(CASE WHEN EnrolmentGradeVAScore_vs_AcYr IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	--	END AS GroupingEnrolmentGradeVAScore_vs_AcYr,
	--CAST(Sum([EnrolmentGradeVAScore_vs_AcYr]) AS DECIMAL(19,14)) AS GroupingEnrolmentGradeVAScore_vs_AcYr_Dividend,
	--Sum(CASE WHEN EnrolmentGradeVAScore_vs_AcYr IS NULL THEN 0 ELSE 1 END) AS GroupingEnrolmentGradeVAScore_vs_AcYr_Divisor,

	CASE WHEN Sum(CASE WHEN EnrolmentGradeVAScore_vs_MostRecent IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL 
		ELSE CAST(Sum([EnrolmentGradeVAScore_vs_MostRecent]) / Sum(CASE WHEN EnrolmentGradeVAScore_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
		END AS GroupingEnrolmentGradeVAScore_vs_MostRecent,
	CAST(Sum([EnrolmentGradeVAScore_vs_MostRecent]) AS DECIMAL(19,14)) AS GroupingEnrolmentGradeVAScore_vs_MostRecent_Dividend,
	Sum(CASE WHEN EnrolmentGradeVAScore_vs_MostRecent IS NULL THEN 0 ELSE 1 END) AS GroupingEnrolmentGradeVAScore_vs_MostRecent_Divisor,

	CASE WHEN Sum(CASE WHEN EnrolmentPoints IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL 
		ELSE CAST(Sum([EnrolmentPoints]) / Sum(CASE WHEN EnrolmentPoints IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
		END AS GroupingEnrolmentPoints,
	CAST(Sum([EnrolmentPoints]) AS DECIMAL(19,14)) AS GroupingEnrolmentPoints_Dividend,
	Sum(CASE WHEN EnrolmentPoints IS NULL THEN 0 ELSE 1 END) AS GroupingEnrolmentPoints_Divisor,

	--CASE WHEN Sum(CASE WHEN [EstPoints_AcYr] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
	--	ELSE CAST(Sum([EstPoints_AcYr]) / Sum(CASE WHEN [EstPoints_AcYr] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2))
	--END AS GroupingEstPoints_AcYr,
	--CAST(Sum([EstPoints_AcYr]) AS DECIMAL(19,14)) AS GroupingEstPoints_AcYr_Dividend,
	--Sum(CASE WHEN [EstPoints_AcYr] IS NULL THEN 0 ELSE 1 END) AS GroupingEstPoints_AcYr_Divisor,

	/*
	CASE WHEN VA_Type = 'CA' THEN
		COALESCE((SELECT #tStudent.CountOfStudent FROM #tStudent WHERE #Temp.AcademicYearID = #tStudent.AcademicYearID AND #Temp.VA_Type = #tStudent.VA_Type AND #Temp.Cohort = #tStudent.Cohort AND #Temp.CA_QualTypeID = #tStudent.CA_QualTypeID AND #Temp.Grouping1 = #tStudent.Grouping1 AND #Temp.Grouping2 = #tStudent.Grouping2 AND #Temp.Grouping3 = #tStudent.Grouping3 AND #Temp.Grouping4 = #tStudent.Grouping4),0)
	WHEN VA_Type = 'Pass Only' THEN
		COALESCE((SELECT #tStudent.CountOfStudent FROM #tStudent WHERE #Temp.AcademicYearID = #tStudent.AcademicYearID AND #Temp.VA_Type = #tStudent.VA_Type AND #Temp.Cohort = #tStudent.Cohort AND #Temp.VA_Type = #tStudent.VA_Type AND #Temp.VA_Type = 'Pass Only' AND #Temp.Grouping1 = #tStudent.Grouping1 AND #Temp.Grouping2 = #tStudent.Grouping2 AND #Temp.Grouping3 = #tStudent.Grouping3 AND #Temp.Grouping4 = #tStudent.Grouping4),0)
	WHEN VA_Type = 'L3VA' THEN
		COALESCE((SELECT #tStudent.CountOfStudent FROM #tStudent WHERE #Temp.AcademicYearID = #tStudent.AcademicYearID AND #Temp.VA_Type = #tStudent.VA_Type AND #Temp.Cohort = #tStudent.Cohort AND #Temp.QualificationID = #tStudent.QualificationID AND #Temp.Grouping1 = #tStudent.Grouping1 AND #Temp.Grouping2 = #tStudent.Grouping2 AND #Temp.Grouping3 = #tStudent.Grouping3 AND #Temp.Grouping4 = #tStudent.Grouping4),0)
	WHEN VA_Type = 'Ofqual Graded' THEN
		COALESCE((SELECT #tStudent.CountOfStudent FROM #tStudent WHERE #Temp.AcademicYearID = #tStudent.AcademicYearID AND #Temp.VA_Type = #tStudent.VA_Type AND #Temp.Cohort = #tStudent.Cohort AND #Temp.VA_Type = #tStudent.VA_Type AND #Temp.OfqualGradingScheme = #tStudent.OfqualGradingScheme AND #Temp.Grouping1 = #tStudent.Grouping1 AND #Temp.Grouping2 = #tStudent.Grouping2 AND #Temp.Grouping3 = #tStudent.Grouping3 AND #Temp.Grouping4 = #tStudent.Grouping4),0)
	WHEN VA_Type = 'NoGrade' THEN
		COALESCE((SELECT #tStudent.CountOfStudent FROM #tStudent WHERE #Temp.AcademicYearID = #tStudent.AcademicYearID AND #Temp.VA_Type = #tStudent.VA_Type AND #Temp.Cohort = #tStudent.Cohort AND #Temp.VA_Type = #tStudent.VA_Type AND #Temp.VA_Type = 'NoGrade' AND #Temp.Grouping1 = #tStudent.Grouping1 AND #Temp.Grouping2 = #tStudent.Grouping2 AND #Temp.Grouping3 = #tStudent.Grouping3 AND #Temp.Grouping4 = #tStudent.Grouping4),0)
	ELSE
		NULL
	END AS CountOfStudent,
	*/
	SUM(UniqueStudentCount) AS CountOfStudent,

	--NULL AS CountofStudent1 --COALESCE((SELECT #tStudent.CountOfStudent FROM #tStudent WHERE #Temp.AcademicYearID = #tStudent.AcademicYearID AND #Temp.Grouping1 = #tStudent.Grouping1 AND #Temp.Grouping2 = #tStudent.Grouping2 AND #Temp.Grouping3 = #tStudent.Grouping3 AND #Temp.Grouping4 = #tStudent.Grouping4 ),0) AS CountOfStudent1
	
	--STDEVP(MostRecentCollegeEstPoints) AS MostRecentCollegeEstPoints_STDEVP,
	--STDEVP(EnrolmentGradeVAScore_vs_MostRecent) AS EnrolmentGradeVAScore_vs_MostRecent_STDEVP,
	
	--STDEVP(IYMostRecent_vs_RecentTarget) AS IYMostRecent_vs_RecentTarget_STDEVP,
	--STDEVP(Point1_vs_MostRecent) AS Point1_vs_RecentTarget_STDEVP,
	--STDEVP(Point2_vs_MostRecent) AS Point2_vs_RecentTarget_STDEVP,
	--STDEVP(Point3_vs_MostRecent) AS Point3_vs_RecentTarget_STDEVP,
	--STDEVP(Point4_vs_MostRecent) AS Point4_vs_RecentTarget_STDEVP,
	--STDEVP(Point5_vs_MostRecent) AS Point5_vs_RecentTarget_STDEVP,
	--STDEVP(Point6_vs_MostRecent) AS Point6_vs_RecentTarget_STDEVP,
	--STDEVP(Point7_vs_MostRecent) AS Point7_vs_RecentTarget_STDEVP,
	--STDEVP(Point8_vs_MostRecent) AS Point8_vs_RecentTarget_STDEVP,

	--Added 13/06/2019 v1.13 / 1.14 Modified - only count MostRecentCollegeEstPoints when we have IY1, 2, 3, 4, Most Recent points:
	CASE WHEN Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point1_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum(CASE WHEN [Point1_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) / Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point1_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingMostRecentCollegeEstPoints_forPt1IYEnrols,
	CAST(Sum(CASE WHEN [Point1_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) AS DECIMAL(19,14)) AS GroupingMostRecentCollegeEstPoints_forPt1IYEnrols_Dividend,
	Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point1_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingMostRecentCollegeEstPoints_forPt1IYEnrols_Divisor,

	CASE WHEN Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point2_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum(CASE WHEN [Point2_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) / Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point2_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingMostRecentCollegeEstPoints_forPt2IYEnrols,
	CAST(Sum(CASE WHEN [Point2_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) AS DECIMAL(19,14)) AS GroupingMostRecentCollegeEstPoints_forPt2IYEnrols_Dividend,
	Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point2_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingMostRecentCollegeEstPoints_forPt2IYEnrols_Divisor,

	CASE WHEN Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point3_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum(CASE WHEN [Point3_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) / Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point3_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingMostRecentCollegeEstPoints_forPt3IYEnrols,
	CAST(Sum(CASE WHEN [Point3_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) AS DECIMAL(19,14)) AS GroupingMostRecentCollegeEstPoints_forPt3IYEnrols_Dividend,
	Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point3_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingMostRecentCollegeEstPoints_forPt3IYEnrols_Divisor,

	CASE WHEN Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point4_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum(CASE WHEN [Point4_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) / Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point4_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingMostRecentCollegeEstPoints_forPt4IYEnrols,
	CAST(Sum(CASE WHEN [Point4_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) AS DECIMAL(19,14)) AS GroupingMostRecentCollegeEstPoints_forPt4IYEnrols_Dividend,
	Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point4_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingMostRecentCollegeEstPoints_forPt4IYEnrols_Divisor,


	CASE WHEN Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point5_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum(CASE WHEN [Point5_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) / Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point5_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingMostRecentCollegeEstPoints_forPt5IYEnrols,
	CAST(Sum(CASE WHEN [Point5_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) AS DECIMAL(19,14)) AS GroupingMostRecentCollegeEstPoints_forPt5IYEnrols_Dividend,
	Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point5_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingMostRecentCollegeEstPoints_forPt5IYEnrols_Divisor,
	
	CASE WHEN Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point6_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum(CASE WHEN [Point6_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) / Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point6_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingMostRecentCollegeEstPoints_forPt6IYEnrols,
	CAST(Sum(CASE WHEN [Point6_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) AS DECIMAL(19,14)) AS GroupingMostRecentCollegeEstPoints_forPt6IYEnrols_Dividend,
	Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point6_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingMostRecentCollegeEstPoints_forPt6IYEnrols_Divisor,

	CASE WHEN Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point7_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum(CASE WHEN [Point7_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) / Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point7_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingMostRecentCollegeEstPoints_forPt7IYEnrols,
	CAST(Sum(CASE WHEN [Point7_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) AS DECIMAL(19,14)) AS GroupingMostRecentCollegeEstPoints_forPt7IYEnrols_Dividend,
	Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point7_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingMostRecentCollegeEstPoints_forPt7IYEnrols_Divisor,

	CASE WHEN Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point8_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum(CASE WHEN [Point8_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) / Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point8_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingMostRecentCollegeEstPoints_forPt8IYEnrols,
	CAST(Sum(CASE WHEN [Point8_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) AS DECIMAL(19,14)) AS GroupingMostRecentCollegeEstPoints_forPt8IYEnrols_Dividend,
	Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point8_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingMostRecentCollegeEstPoints_forPt8IYEnrols_Divisor,


	CASE WHEN Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point9_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum(CASE WHEN [Point9_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) / Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point9_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingMostRecentCollegeEstPoints_forPt9IYEnrols,
	CAST(Sum(CASE WHEN [Point9_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) AS DECIMAL(19,14)) AS GroupingMostRecentCollegeEstPoints_forPt9IYEnrols_Dividend,
	Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point9_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingMostRecentCollegeEstPoints_forPt9IYEnrols_Divisor,
	
	CASE WHEN Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point10_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum(CASE WHEN [Point10_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) / Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point10_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingMostRecentCollegeEstPoints_forPt10IYEnrols,
	CAST(Sum(CASE WHEN [Point10_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) AS DECIMAL(19,14)) AS GroupingMostRecentCollegeEstPoints_forPt10IYEnrols_Dividend,
	Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point10_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingMostRecentCollegeEstPoints_forPt10IYEnrols_Divisor,

	CASE WHEN Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point11_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum(CASE WHEN [Point11_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) / Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point11_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingMostRecentCollegeEstPoints_forPt11IYEnrols,
	CAST(Sum(CASE WHEN [Point11_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) AS DECIMAL(19,14)) AS GroupingMostRecentCollegeEstPoints_forPt11IYEnrols_Dividend,
	Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point11_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingMostRecentCollegeEstPoints_forPt11IYEnrols_Divisor,

	CASE WHEN Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point12_Points] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum(CASE WHEN [Point12_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) / Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point12_Points] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingMostRecentCollegeEstPoints_forPt12IYEnrols,
	CAST(Sum(CASE WHEN [Point12_Points] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) AS DECIMAL(19,14)) AS GroupingMostRecentCollegeEstPoints_forPt12IYEnrols_Dividend,
	Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [Point12_Points] IS NULL THEN 0 ELSE 1 END) AS GroupingMostRecentCollegeEstPoints_forPt12IYEnrols_Divisor,


	CASE WHEN Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [IYMostRecentPoints] IS NULL THEN 0 ELSE 1 END) = 0 THEN NULL
		ELSE CAST(Sum(CASE WHEN [IYMostRecentPoints] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) / Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [IYMostRecentPoints] IS NULL THEN 0 ELSE 1 END) AS DECIMAL(19,2)) 
	END AS GroupingMostRecentCollegeEstPoints_forMostRecentIYEnrols,
	CAST(Sum(CASE WHEN [IYMostRecentPoints] IS NULL THEN 0 ELSE [MostRecentCollegeEstPoints] END) AS DECIMAL(19,14)) AS GroupingMostRecentCollegeEstPoints_forMostRecentIYEnrols_Dividend,
	Sum(CASE WHEN [MostRecentCollegeEstPoints] IS NULL OR [IYMostRecentPoints] IS NULL THEN 0 ELSE 1 END) AS GroupingMostRecentCollegeEstPoints_forMostRecentIYEnrols_Divisor,

	Sum(CtEnrIYPts) AS CountofEnrolsWithIYPoints,
	SUM(CtEnrEnrolmentPts) AS CountofEnrolsWithEnrolmentPoints



	INTO
	#TempFinal

	FROM
	#Temp

	GROUP BY AcademicYearID, VA_Type, Cohort, CA_QualTypeID , QualificationID, OfqualGradingScheme, Grouping1 , Grouping2, Grouping3, Grouping4 



	--select * from #TempFinal



----****************************


	SELECT
	ID,
	AcademicYearID,
	VA_Type,
	Cohort,
	CASE VA_Type 
		WHEN 'CA' THEN (SELECT CA_QualType.QualTypeDescription FROM CA_QualType WHERE CA_QualType.ID = CA_QualTypeID)
		WHEN 'Pass Only' THEN  'Pass Only'
		WHEN 'Ofqual Graded' THEN OfqualGradingScheme
		WHEN 'NoGrade' THEN 'NoGrade' 
		WHEN 'L3VA' THEN (SELECT QualificationLookup.QualificationDescription FROM QualificationLookup WHERE QualificationLookup.ID = QualificationID)
		ELSE NULL
	END as QualificationType,

	LEFT(Grouping1, 100) AS Grouping1,
	LEFT(Grouping2, 100) AS Grouping2,
	LEFT(Grouping3, 100) AS Grouping3,
	LEFT(Grouping4, 100) AS Grouping4,
	CountOfEnrol,
	GroupingQOEPoints_College,
	GroupingQOEPoints_College_Dividend,
	GroupingQOEPoints_College_Divisor,
	GroupingQOEPoints_GCSE_College,
	GroupingQOEPoints_GCSE_College_Dividend,
	GroupingQOEPoints_GCSE_College_Divisor,

	--GroupingQOEPoints_Final,
	--GroupingQOEPoints_Final_Dividend,
	--GroupingQOEPoints_Final_Divisor,

	--GroupingQOEPoints_GCSE_Final,
	--GroupingQOEPoints_GCSE_Final_Dividend,
	--GroupingQOEPoints_GCSE_Final_Divisor,

	--GroupingEstPoints_AcYrMin3,
	--GroupingEstPoints_AcYrMin3_Dividend,
	--GroupingEstPoints_AcYrMin3_Divisor,

	--CASE WHEN GroupingEstPoints_AcYrMin3 IS NULL THEN NULL ELSE
	--	CASE VA_Type WHEN 'CA' THEN
	--		[dbo].[fn_CA_CalculateGrade](CA_QualTypeID, GroupingEstPoints_AcYrMin3, Cohort)
	--	ELSE
	--		[dbo].[fn_CalculateGrade](QualificationID, GroupingEstPoints_AcYrMin3 )
	--	END 
	--END
	--AS [GroupingEstGrade_AcYrMin3],

	--GroupingEstPoints_AcYrMin2,
	--GroupingEstPoints_AcYrMin2_Dividend,
	--GroupingEstPoints_AcYrMin2_Divisor,

	--CASE WHEN GroupingEstPoints_AcYrMin2 IS NULL THEN NULL ELSE
	--	CASE VA_Type WHEN 'CA' THEN
	--		[dbo].[fn_CA_CalculateGrade](CA_QualTypeID, GroupingEstPoints_AcYrMin2, Cohort)
	--	ELSE
	--		[dbo].[fn_CalculateGrade](QualificationID, GroupingEstPoints_AcYrMin2 )
	--	END 
	--END
	--AS [GroupingEstGrade_AcYrMin2],
	
	--GroupingEstPoints_AcYrMin1,
	--GroupingEstPoints_AcYrMin1_Dividend,
	--GroupingEstPoints_AcYrMin1_Divisor,

	--CASE WHEN GroupingEstPoints_AcYrMin1 IS NULL THEN NULL ELSE
	--	CASE VA_Type WHEN 'CA' THEN
	--		[dbo].[fn_CA_CalculateGrade](CA_QualTypeID, GroupingEstPoints_AcYrMin1, Cohort)
	--	ELSE
	--		[dbo].[fn_CalculateGrade](QualificationID, GroupingEstPoints_AcYrMin1 )
	--	END 
	--END
	--AS [GroupingEstGrade_AcYrMin1],

	--GroupingVAScore_vs_Orig,
	--GroupingVAScore_vs_Orig_Dividend,
	--GroupingVAScore_vs_Orig_Divisor,
	
	--GroupingVAScore_vs_Adj,
	--GroupingVAScore_vs_Adj_Dividend,
	--GroupingVAScore_vs_Adj_Divisor,
	
	--GroupingVAScore_L3VAStu,
	--GroupingVAScore_L3VAStu_Dividend,
	--GroupingVAScore_L3VAStu_Divisor,
	
	--GroupingEstPoints_L3VAStu,
	--GroupingEstPoints_L3VAStu_Dividend,
	--GroupingEstPoints_L3VAStu_Divisor,

	--CASE WHEN GroupingEstPoints_L3VAStu IS NULL THEN NULL ELSE
	--	CASE VA_Type WHEN 'CA' THEN
	--		[dbo].[fn_CA_CalculateGrade](CA_QualTypeID, GroupingEstPoints_L3VAStu, Cohort)
	--	ELSE
	--		[dbo].[fn_CalculateGrade](QualificationID, GroupingEstPoints_L3VAStu )
	--	END
	--END AS [GroupingEstGrade_L3VAStu],

	--GroupingActPoints_L3VAStu,
	--GroupingActPoints_L3VAStu_Dividend,
	--GroupingActPoints_L3VAStu_Divisor,

	--CASE WHEN GroupingActPoints_L3VAStu IS NULL THEN NULL ELSE
	--	CASE VA_Type WHEN 'CA' THEN
	--		[dbo].[fn_CA_CalculateGrade](CA_QualTypeID, GroupingActPoints_L3VAStu, Cohort)
	--	ELSE
	--		[dbo].[fn_CalculateGrade](QualificationID, GroupingActPoints_L3VAStu )
	--	END
	--END AS [GroupingActGrade_L3VAStu],
	
	--GroupingEnrolmentGradeVAScore_vs_Orig,
	--GroupingEnrolmentGradeVAScore_vs_Orig_Dividend,
	--GroupingEnrolmentGradeVAScore_vs_Orig_Divisor,

	--GroupingEnrolmentGradeVAScore_vs_Adj,
	--GroupingEnrolmentGradeVAScore_vs_Adj_Dividend,
	--GroupingEnrolmentGradeVAScore_vs_Adj_Divisor,

	GroupingIYMostRecentPoints,
	GroupingIYMostRecentPoints_Dividend,
	GroupingIYMostRecentPoints_Divisor,

	CASE WHEN GroupingIYMostRecentPoints IS NULL THEN NULL ELSE
		CASE WHEN VA_Type  = 'CA' THEN
			[dbo].[fn_CA_CalculateGrade](CA_QualTypeID, GroupingIYMostRecentPoints, Cohort)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGrade_PassOnly(GroupingIYMostRecentPoints)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGrade](QualificationID, GroupingIYMostRecentPoints )
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGrade_OfqualGraded(OfqualGradingScheme, GroupingIYMostRecentPoints) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGrade_NoGrade(GroupingIYMostRecentPoints)
		ELSE	
			NULL
		END 
	END AS [GroupingIYMostRecentGrade],

	CASE WHEN GroupingIYMostRecentPoints IS NULL THEN NULL ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGradeNo](CA_QualTypeID, GroupingIYMostRecentPoints, Cohort)
		WHEN VA_Type = 'Pass Only' THEN 
			[dbo].fn_CalculateGradeNo_PassOnly(GroupingIYMostRecentPoints)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGradeNo](QualificationID, GroupingIYMostRecentPoints )
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, GroupingIYMostRecentPoints) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGradeNo_NoGrade(GroupingIYMostRecentPoints)
		ELSE
			NULL
		END  
	END AS [GroupingIYMostRecentGradeNo],

	CASE WHEN GroupingIYMostRecentPoints IS NULL THEN NULL ELSE 
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the lower the GradeNo, the higher the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYMostRecentGradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4  
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN 
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYMostRecentGradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYMostRecentGradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYMostRecentGradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END 
	END AS [GroupingIYMostRecentAboveEstGradeNo],

	CASE WHEN GroupingIYMostRecentPoints IS NULL THEN NULL ELSE 
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYMostRecentGradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN 
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYMostRecentGradeNo >  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYMostRecentGradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYMostRecentGradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE 
			NULL
		END 
	END AS [GroupingIYMostRecentBelowEstGradeNo],

	CASE WHEN GroupingIYMostRecentPoints IS NULL THEN NULL ELSE 
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYMostRecentGradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN 
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYMostRecentGradeNo =  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYMostRecentGradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYMostRecentGradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYMostRecentEqualEstGradeNo],

	GroupingIYMostRecentPoints_Divisor AS CountofIYEnrol, --the divisor is the same as the number of enrols.

	--GroupingIYMostRecent_vs_Orig,
	--GroupingIYMostRecent_vs_Orig_Dividend,
	--GroupingIYMostRecent_vs_Orig_Divisor,
	--GroupingIYMostRecent_vs_Adj,
	--GroupingIYMostRecent_vs_Adj_Dividend,
	--GroupingIYMostRecent_vs_Adj_Divisor,

	GroupingIYMostRecent_vs_RecentTarget,
	GroupingIYMostRecent_vs_RecentTarget_Dividend,
	GroupingIYMostRecent_vs_RecentTarget_Divisor,

	CASE WHEN VA_Type = 'L3VA' THEN
		CASE WHEN GroupingIYMostRecent_vs_RecentTarget = 0 THEN 'Avg' 
		ELSE
			(SELECT TOP(1) BandDescription FROM NationalProgress 
							WHERE GroupingIYMostRecent_vs_RecentTarget >= NationalProgress.LowerLimit 
							AND GroupingIYMostRecent_vs_RecentTarget < NationalProgress.UpperLimit
							AND NationalProgress.CohortName = #TempFinal.Cohort 
							AND NationalProgress.AcademicYearID = @AcademicYearID_NationalProgress
			)
		END
	ELSE
		NULL
	END AS GroupingIYMostRecent_vs_RecentTarget_NationalBanding,
	
	GroupingPoint1Points,
	GroupingPoint1Points_Dividend,
	GroupingPoint1Points_Divisor,
	CASE WHEN GroupingPoint1Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGrade](CA_QualTypeID, GroupingPoint1Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGrade](QualificationID, GroupingPoint1Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGrade_PassOnly(GroupingPoint1Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGrade_OfqualGraded(OfqualGradingScheme, GroupingPoint1Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGrade_NoGrade(GroupingPoint1Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint1Grade],

	CASE WHEN GroupingPoint1Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGradeNo](CA_QualTypeID, GroupingPoint1Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGradeNo](QualificationID, GroupingPoint1Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGradeNo_PassOnly(GroupingPoint1Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, GroupingPoint1Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGradeNo_NoGrade(GroupingPoint1Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint1GradeNo],
	GroupingPoint1_vs_RecentTarget,
	GroupingPoint1_vs_RecentTarget_Dividend,
	GroupingPoint1_vs_RecentTarget_Divisor,
	GroupingPoint1Points_Divisor AS CountofIYEnrolPoint1, --the divisor is the same as the number of enrols.

	CASE WHEN GroupingPoint1Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the lower the GradeNo, the higher the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint1GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint1GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint1GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint1GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint1AboveEstGradeNo],

	CASE WHEN GroupingPoint1Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint1GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint1GradeNo >  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint1GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint1GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint1BelowEstGradeNo],

	CASE WHEN GroupingPoint1Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint1GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint1GradeNo =  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN 
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint1GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint1GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint1EqualEstGradeNo],


	GroupingPoint2Points,
	GroupingPoint2Points_Dividend,
	GroupingPoint2Points_Divisor,
	CASE WHEN GroupingPoint2Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGrade](CA_QualTypeID, GroupingPoint2Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGrade](QualificationID, GroupingPoint2Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGrade_PassOnly(GroupingPoint2Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGrade_OfqualGraded(OfqualGradingScheme, GroupingPoint2Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGrade_NoGrade(GroupingPoint2Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint2Grade],

	CASE WHEN GroupingPoint2Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGradeNo](CA_QualTypeID, GroupingPoint2Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGradeNo](QualificationID, GroupingPoint2Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGradeNo_PassOnly(GroupingPoint2Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, GroupingPoint2Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGradeNo_NoGrade(GroupingPoint2Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint2GradeNo],
	GroupingPoint2_vs_RecentTarget,
	GroupingPoint2_vs_RecentTarget_Dividend,
	GroupingPoint2_vs_RecentTarget_Divisor,
	GroupingPoint2Points_Divisor AS CountofIYEnrolPoint2, --the divisor is the same as the number of enrols.

	CASE WHEN GroupingPoint2Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the lower the GradeNo, the higher the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint2GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint2GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint2GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint2GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint2AboveEstGradeNo],

	CASE WHEN GroupingPoint2Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint2GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint2GradeNo >  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint2GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint2GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint2BelowEstGradeNo],

	CASE WHEN GroupingPoint2Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint2GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint2GradeNo =  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN 
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint2GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint2GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint2EqualEstGradeNo],


	GroupingPoint3Points,
	GroupingPoint3Points_Dividend,
	GroupingPoint3Points_Divisor,
	CASE WHEN GroupingPoint3Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGrade](CA_QualTypeID, GroupingPoint3Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGrade](QualificationID, GroupingPoint3Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGrade_PassOnly(GroupingPoint3Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGrade_OfqualGraded(OfqualGradingScheme, GroupingPoint3Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGrade_NoGrade(GroupingPoint3Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint3Grade],

	CASE WHEN GroupingPoint3Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGradeNo](CA_QualTypeID, GroupingPoint3Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGradeNo](QualificationID, GroupingPoint3Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGradeNo_PassOnly(GroupingPoint3Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, GroupingPoint3Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGradeNo_NoGrade(GroupingPoint3Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint3GradeNo],
	GroupingPoint3_vs_RecentTarget,
	GroupingPoint3_vs_RecentTarget_Dividend,
	GroupingPoint3_vs_RecentTarget_Divisor,
	GroupingPoint3Points_Divisor AS CountofIYEnrolPoint3, --the divisor is the same as the number of enrols.

	CASE WHEN GroupingPoint3Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the lower the GradeNo, the higher the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint3GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint3GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint3GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint3GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint3AboveEstGradeNo],

	CASE WHEN GroupingPoint3Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint3GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint3GradeNo >  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint3GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint3GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint3BelowEstGradeNo],

	CASE WHEN GroupingPoint3Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint3GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint3GradeNo =  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN 
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint3GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint3GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint3EqualEstGradeNo],


	GroupingPoint4Points,
	GroupingPoint4Points_Dividend,
	GroupingPoint4Points_Divisor,
	CASE WHEN GroupingPoint4Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGrade](CA_QualTypeID, GroupingPoint4Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGrade](QualificationID, GroupingPoint4Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGrade_PassOnly(GroupingPoint4Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGrade_OfqualGraded(OfqualGradingScheme, GroupingPoint4Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGrade_NoGrade(GroupingPoint4Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint4Grade],

	CASE WHEN GroupingPoint4Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGradeNo](CA_QualTypeID, GroupingPoint4Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGradeNo](QualificationID, GroupingPoint4Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGradeNo_PassOnly(GroupingPoint4Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, GroupingPoint4Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGradeNo_NoGrade(GroupingPoint4Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint4GradeNo],
	GroupingPoint4_vs_RecentTarget,
	GroupingPoint4_vs_RecentTarget_Dividend,
	GroupingPoint4_vs_RecentTarget_Divisor,
	GroupingPoint4Points_Divisor AS CountofIYEnrolPoint4, --the divisor is the same as the number of enrols.

	CASE WHEN GroupingPoint4Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the lower the GradeNo, the higher the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint4GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint4GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint4GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint4GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint4AboveEstGradeNo],

	CASE WHEN GroupingPoint4Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint4GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint4GradeNo >  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint4GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint4GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint4BelowEstGradeNo],

	CASE WHEN GroupingPoint4Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint4GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint4GradeNo =  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN 
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint4GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint4GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint4EqualEstGradeNo],


	GroupingPoint5Points,
	GroupingPoint5Points_Dividend,
	GroupingPoint5Points_Divisor,
	CASE WHEN GroupingPoint5Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGrade](CA_QualTypeID, GroupingPoint5Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGrade](QualificationID, GroupingPoint5Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGrade_PassOnly(GroupingPoint5Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGrade_OfqualGraded(OfqualGradingScheme, GroupingPoint5Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGrade_NoGrade(GroupingPoint5Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint5Grade],

	CASE WHEN GroupingPoint5Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGradeNo](CA_QualTypeID, GroupingPoint5Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGradeNo](QualificationID, GroupingPoint5Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGradeNo_PassOnly(GroupingPoint5Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, GroupingPoint5Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGradeNo_NoGrade(GroupingPoint5Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint5GradeNo],
	GroupingPoint5_vs_RecentTarget,
	GroupingPoint5_vs_RecentTarget_Dividend,
	GroupingPoint5_vs_RecentTarget_Divisor,
	GroupingPoint5Points_Divisor AS CountofIYEnrolPoint5, --the divisor is the same as the number of enrols.

	CASE WHEN GroupingPoint5Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the lower the GradeNo, the higher the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint5GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint5GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint5GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint5GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint5AboveEstGradeNo],

	CASE WHEN GroupingPoint5Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint5GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint5GradeNo >  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint5GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint5GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint5BelowEstGradeNo],

	CASE WHEN GroupingPoint5Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint5GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint5GradeNo =  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN 
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint5GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint5GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint5EqualEstGradeNo],

	GroupingPoint6Points,
	GroupingPoint6Points_Dividend,
	GroupingPoint6Points_Divisor,
	CASE WHEN GroupingPoint6Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGrade](CA_QualTypeID, GroupingPoint6Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGrade](QualificationID, GroupingPoint6Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGrade_PassOnly(GroupingPoint6Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGrade_OfqualGraded(OfqualGradingScheme, GroupingPoint6Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGrade_NoGrade(GroupingPoint6Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint6Grade],

	CASE WHEN GroupingPoint6Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGradeNo](CA_QualTypeID, GroupingPoint6Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGradeNo](QualificationID, GroupingPoint6Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGradeNo_PassOnly(GroupingPoint6Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, GroupingPoint6Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGradeNo_NoGrade(GroupingPoint6Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint6GradeNo],
	GroupingPoint6_vs_RecentTarget,
	GroupingPoint6_vs_RecentTarget_Dividend,
	GroupingPoint6_vs_RecentTarget_Divisor,
	GroupingPoint6Points_Divisor AS CountofIYEnrolPoint6, --the divisor is the same as the number of enrols.

	CASE WHEN GroupingPoint6Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the lower the GradeNo, the higher the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint6GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint6GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint6GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint6GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint6AboveEstGradeNo],

	CASE WHEN GroupingPoint6Points IS NULL THEN NULL
		ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint6GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint6GradeNo >  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint6GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint6GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint6BelowEstGradeNo],

	CASE WHEN GroupingPoint6Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint6GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint6GradeNo =  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN 
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint6GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint6GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint6EqualEstGradeNo],

	GroupingPoint7Points,
	GroupingPoint7Points_Dividend,
	GroupingPoint7Points_Divisor,
	CASE WHEN GroupingPoint7Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGrade](CA_QualTypeID, GroupingPoint7Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGrade](QualificationID, GroupingPoint7Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGrade_PassOnly(GroupingPoint7Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGrade_OfqualGraded(OfqualGradingScheme, GroupingPoint7Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGrade_NoGrade(GroupingPoint7Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint7Grade],

	CASE WHEN GroupingPoint7Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGradeNo](CA_QualTypeID, GroupingPoint7Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGradeNo](QualificationID, GroupingPoint7Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGradeNo_PassOnly(GroupingPoint7Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, GroupingPoint7Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGradeNo_NoGrade(GroupingPoint7Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint7GradeNo],
	GroupingPoint7_vs_RecentTarget,
	GroupingPoint7_vs_RecentTarget_Dividend,
	GroupingPoint7_vs_RecentTarget_Divisor,
	GroupingPoint7Points_Divisor AS CountofIYEnrolPoint7, --the divisor is the same as the number of enrols.

	CASE WHEN GroupingPoint7Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the lower the GradeNo, the higher the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint7GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint7GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint7GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint7GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint7AboveEstGradeNo],

	CASE WHEN GroupingPoint7Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint7GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint7GradeNo >  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint7GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint7GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint7BelowEstGradeNo],

	CASE WHEN GroupingPoint7Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint7GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint7GradeNo =  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN 
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint7GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint7GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint7EqualEstGradeNo],

	GroupingPoint8Points,
	GroupingPoint8Points_Dividend,
	GroupingPoint8Points_Divisor,
	CASE WHEN GroupingPoint8Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGrade](CA_QualTypeID, GroupingPoint8Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGrade](QualificationID, GroupingPoint8Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGrade_PassOnly(GroupingPoint8Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGrade_OfqualGraded(OfqualGradingScheme, GroupingPoint8Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGrade_NoGrade(GroupingPoint8Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint8Grade],

	CASE WHEN GroupingPoint8Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGradeNo](CA_QualTypeID, GroupingPoint8Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGradeNo](QualificationID, GroupingPoint8Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGradeNo_PassOnly(GroupingPoint8Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, GroupingPoint8Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGradeNo_NoGrade(GroupingPoint8Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint8GradeNo],
	GroupingPoint8_vs_RecentTarget,
	GroupingPoint8_vs_RecentTarget_Dividend,
	GroupingPoint8_vs_RecentTarget_Divisor,
	GroupingPoint8Points_Divisor AS CountofIYEnrolPoint8, --the divisor is the same as the number of enrols.

	CASE WHEN GroupingPoint8Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the lower the GradeNo, the higher the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint8GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint8GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint8GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint8GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint8AboveEstGradeNo],

	CASE WHEN GroupingPoint8Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint8GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint8GradeNo >  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint8GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint8GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint8BelowEstGradeNo],

	CASE WHEN GroupingPoint8Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint8GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint8GradeNo =  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN 
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint8GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint8GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint8EqualEstGradeNo],


	-----------------------------------------------------------------------------------
	GroupingPoint9Points,
	GroupingPoint9Points_Dividend,
	GroupingPoint9Points_Divisor,
	CASE WHEN GroupingPoint9Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGrade](CA_QualTypeID, GroupingPoint9Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGrade](QualificationID, GroupingPoint9Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGrade_PassOnly(GroupingPoint9Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGrade_OfqualGraded(OfqualGradingScheme, GroupingPoint9Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGrade_NoGrade(GroupingPoint9Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint9Grade],

	CASE WHEN GroupingPoint9Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGradeNo](CA_QualTypeID, GroupingPoint9Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGradeNo](QualificationID, GroupingPoint9Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGradeNo_PassOnly(GroupingPoint9Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, GroupingPoint9Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGradeNo_NoGrade(GroupingPoint9Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint9GradeNo],
	GroupingPoint9_vs_RecentTarget,
	GroupingPoint9_vs_RecentTarget_Dividend,
	GroupingPoint9_vs_RecentTarget_Divisor,
	GroupingPoint9Points_Divisor AS CountofIYEnrolPoint9, --the divisor is the same as the number of enrols.

	CASE WHEN GroupingPoint9Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the lower the GradeNo, the higher the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint9GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint9GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint9GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint9GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint9AboveEstGradeNo],

	CASE WHEN GroupingPoint9Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint9GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint9GradeNo >  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint9GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint9GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint9BelowEstGradeNo],

	CASE WHEN GroupingPoint9Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint9GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint9GradeNo =  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN 
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint9GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint9GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint9EqualEstGradeNo],

	---------------------------------------------------------------------------------

	GroupingPoint10Points,
	GroupingPoint10Points_Dividend,
	GroupingPoint10Points_Divisor,
	CASE WHEN GroupingPoint10Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGrade](CA_QualTypeID, GroupingPoint10Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGrade](QualificationID, GroupingPoint10Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGrade_PassOnly(GroupingPoint10Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGrade_OfqualGraded(OfqualGradingScheme, GroupingPoint10Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGrade_NoGrade(GroupingPoint10Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint10Grade],

	CASE WHEN GroupingPoint10Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGradeNo](CA_QualTypeID, GroupingPoint10Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGradeNo](QualificationID, GroupingPoint10Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGradeNo_PassOnly(GroupingPoint10Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, GroupingPoint10Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGradeNo_NoGrade(GroupingPoint10Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint10GradeNo],
	GroupingPoint10_vs_RecentTarget,
	GroupingPoint10_vs_RecentTarget_Dividend,
	GroupingPoint10_vs_RecentTarget_Divisor,
	GroupingPoint10Points_Divisor AS CountofIYEnrolPoint10, --the divisor is the same as the number of enrols.

	CASE WHEN GroupingPoint10Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the lower the GradeNo, the higher the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint10GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint10GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint10GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint10GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint10AboveEstGradeNo],

	CASE WHEN GroupingPoint10Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint10GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint10GradeNo >  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint10GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint10GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint10BelowEstGradeNo],

	CASE WHEN GroupingPoint10Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint10GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint10GradeNo =  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN 
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint10GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint10GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint10EqualEstGradeNo],

	---------------------------------------------------------------------------------


	GroupingPoint11Points,
	GroupingPoint11Points_Dividend,
	GroupingPoint11Points_Divisor,
	CASE WHEN GroupingPoint11Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGrade](CA_QualTypeID, GroupingPoint11Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGrade](QualificationID, GroupingPoint11Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGrade_PassOnly(GroupingPoint11Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGrade_OfqualGraded(OfqualGradingScheme, GroupingPoint11Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGrade_NoGrade(GroupingPoint11Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint11Grade],

	CASE WHEN GroupingPoint11Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGradeNo](CA_QualTypeID, GroupingPoint11Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGradeNo](QualificationID, GroupingPoint11Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGradeNo_PassOnly(GroupingPoint11Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, GroupingPoint11Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGradeNo_NoGrade(GroupingPoint11Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint11GradeNo],
	GroupingPoint11_vs_RecentTarget,
	GroupingPoint11_vs_RecentTarget_Dividend,
	GroupingPoint11_vs_RecentTarget_Divisor,
	GroupingPoint11Points_Divisor AS CountofIYEnrolPoint11, --the divisor is the same as the number of enrols.

	CASE WHEN GroupingPoint11Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the lower the GradeNo, the higher the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint11GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint11GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint11GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint11GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint11AboveEstGradeNo],

	CASE WHEN GroupingPoint11Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint11GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint11GradeNo >  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint11GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint11GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint11BelowEstGradeNo],

	CASE WHEN GroupingPoint11Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint11GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint11GradeNo =  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN 
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint11GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint11GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint11EqualEstGradeNo],

	---------------------------------------------------------------------------------

	GroupingPoint12Points,
	GroupingPoint12Points_Dividend,
	GroupingPoint12Points_Divisor,
	CASE WHEN GroupingPoint12Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGrade](CA_QualTypeID, GroupingPoint12Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGrade](QualificationID, GroupingPoint12Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGrade_PassOnly(GroupingPoint12Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGrade_OfqualGraded(OfqualGradingScheme, GroupingPoint12Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGrade_NoGrade(GroupingPoint12Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint12Grade],

	CASE WHEN GroupingPoint12Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGradeNo](CA_QualTypeID, GroupingPoint12Points, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGradeNo](QualificationID, GroupingPoint12Points)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGradeNo_PassOnly(GroupingPoint12Points)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGradeNo_OfqualGraded(OfqualGradingScheme, GroupingPoint12Points) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGradeNo_NoGrade(GroupingPoint12Points)
		ELSE
			NULL
		END
	END AS [GroupingPoint12GradeNo],
	GroupingPoint12_vs_RecentTarget,
	GroupingPoint12_vs_RecentTarget_Dividend,
	GroupingPoint12_vs_RecentTarget_Divisor,
	GroupingPoint12Points_Divisor AS CountofIYEnrolPoint12, --the divisor is the same as the number of enrols.

	CASE WHEN GroupingPoint12Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the lower the GradeNo, the higher the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint12GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint12GradeNo <  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint12GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint12GradeNo < #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint12AboveEstGradeNo],

	CASE WHEN GroupingPoint12Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint12GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint12GradeNo >  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint12GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint12GradeNo > #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint12BelowEstGradeNo],

	CASE WHEN GroupingPoint12Points IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN -- Remember that the higher the GradeNo, the lower the grade
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint12GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.CA_QualTypeID = #TempFinal.CA_QualTypeID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type IN ('Pass Only', 'NoGrade') THEN
			(SELECT Count(ID) FROM #Temp WHERE #Temp.IYPoint12GradeNo =  #Temp.MostRecentCollegeGradeNo 
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'L3VA' THEN 
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint12GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.QualificationID = #TempFinal.QualificationID
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4
			)
		WHEN VA_Type = 'Ofqual Graded' THEN
			(SELECT COUNT(ID) FROM #Temp WHERE #Temp.IYPoint12GradeNo = #Temp.MostRecentCollegeGradeNo
			AND #Temp.AcademicYearID = #TempFinal.AcademicYearID
			AND #Temp.Cohort = #TempFinal.Cohort
			AND #Temp.OfqualGradingScheme = #TempFinal.OfqualGradingScheme 
			AND #Temp.Grouping1 = #TempFinal.Grouping1 AND #Temp.Grouping2 = #TempFinal.Grouping2 AND #Temp.Grouping3 = #TempFinal.Grouping3 AND #Temp.Grouping4 = #TempFinal.Grouping4 
			)
		ELSE
			NULL
		END
	END AS [GroupingIYPoint12EqualEstGradeNo],

	---------------------------------------------------------------------------------




	GroupingMostRecentCollegeEstPoints,
	GroupingMostRecentCollegeEstPoints_Dividend,
	GroupingMostRecentCollegeEstPoints_Divisor,

	CASE WHEN GroupingMostRecentCollegeEstPoints IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGrade](CA_QualTypeID, GroupingMostRecentCollegeEstPoints, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGrade](QualificationID, GroupingMostRecentCollegeEstPoints)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGrade_PassOnly(GroupingMostRecentCollegeEstPoints)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGrade_OfqualGraded(OfqualGradingScheme, GroupingMostRecentCollegeEstPoints) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGrade_NoGrade(GroupingMostRecentCollegeEstPoints)
		ELSE
			NULL
		END
	END AS [GroupingMostRecentCollegeEstGrade],

	GroupingVAScore_vs_MostRecent,
	GroupingVAScore_vs_MostRecent_Dividend,
	GroupingVAScore_vs_MostRecent_Divisor,

	--GroupingEnrolmentGradeVAScore_vs_AcYr,
	--GroupingEnrolmentGradeVAScore_vs_AcYr_Dividend,
	--GroupingEnrolmentGradeVAScore_vs_AcYr_Divisor,

	GroupingEnrolmentGradeVAScore_vs_MostRecent,
	GroupingEnrolmentGradeVAScore_vs_MostRecent_Dividend,
	GroupingEnrolmentGradeVAScore_vs_MostRecent_Divisor,

	CASE WHEN VA_Type = 'L3VA' THEN
		CASE WHEN GroupingEnrolmentGradeVAScore_vs_MostRecent = 0 THEN 'Avg' 
		ELSE
			(SELECT TOP(1) BandDescription FROM NationalProgress 
							WHERE GroupingEnrolmentGradeVAScore_vs_MostRecent >= NationalProgress.LowerLimit 
							AND GroupingEnrolmentGradeVAScore_vs_MostRecent < NationalProgress.UpperLimit
							AND NationalProgress.CohortName = #TempFinal.Cohort 
							AND NationalProgress.AcademicYearID = @AcademicYearID_NationalProgress
			)
		END
	ELSE
		NULL
	END AS GroupingEnrolmentGradeVAScore_vs_MostRecent_NationalBanding,

	GroupingEnrolmentPoints,
	GroupingEnrolmentPoints_Dividend,
	GroupingEnrolmentPoints_Divisor,
	CASE WHEN GroupingEnrolmentPoints IS NULL THEN NULL
	ELSE
		CASE WHEN VA_Type = 'CA' THEN
			[dbo].[fn_CA_CalculateGrade](CA_QualTypeID, GroupingEnrolmentPoints, Cohort)
		WHEN VA_Type = 'L3VA' THEN
			[dbo].[fn_CalculateGrade](QualificationID, GroupingEnrolmentPoints)
		WHEN VA_Type = 'Pass Only' THEN
			[dbo].fn_CalculateGrade_PassOnly(GroupingEnrolmentPoints)
		WHEN VA_Type = 'Ofqual Graded' THEN
			[dbo].fn_CalculateGrade_OfqualGraded(OfqualGradingScheme, GroupingEnrolmentPoints) 
		WHEN VA_Type = 'NoGrade' THEN
			[dbo].fn_CalculateGrade_NoGrade(GroupingEnrolmentPoints)
		ELSE
			NULL
		END
	END AS [GroupingEnrolmentGrade],

	CountOfStudent,
	CA_QualTypeID,
	QualificationID,

	--GroupingEstPoints_AcYr,
	--GroupingEstPoints_AcYr_Dividend,
	--GroupingEstPoints_AcYr_Divisor,
	--CASE WHEN GroupingEstPoints_AcYr IS NULL THEN NULL
	--ELSE
	--	CASE VA_Type WHEN 'CA' THEN
	--		[dbo].[fn_CA_CalculateGrade](CA_QualTypeID, GroupingEstPoints_AcYr, Cohort)
	--		WHEN 'Pass Only' THEN
	--		[dbo].fn_CalculateGrade_PassOnly(GroupingEstPoints_AcYr)
	--	ELSE
	--		[dbo].[fn_CalculateGrade](QualificationID, GroupingEstPoints_AcYr )
	--	END
	--END AS [GroupingEstGrade_AcYr],

	--CAST (MostRecentCollegeEstPoints_STDEVP AS DECIMAL(19,2)) AS MostRecentPoints_STDEVP,
	--CASE ISNULL(GroupingMostRecentCollegeEstPoints,0) WHEN 0 THEN 0 ELSE
	--CAST( (MostRecentCollegeEstPoints_STDEVP/GroupingMostRecentCollegeEstPoints) * 100 AS DECIMAL(19,2)) 
	--END AS MostRecentPoints_STDEVP_AsPercentage,

	--CAST (EnrolmentGradeVAScore_vs_MostRecent_STDEVP AS DECIMAL(19,2)) AS EnrolmentGradeVA_MostRecent_STDEVP,

	--CAST (IYMostRecent_vs_RecentTarget_STDEVP AS DECIMAL(19,2)) AS IYMostRecentVA_STDEVP,
	--CAST (Point1_vs_RecentTarget_STDEVP AS DECIMAL(19,2)) AS IY1VA_STDEVP,
	--CAST (Point2_vs_RecentTarget_STDEVP AS DECIMAL(19,2)) AS IY2VA_STDEVP,
	--CAST (Point3_vs_RecentTarget_STDEVP AS DECIMAL(19,2)) AS IY3VA_STDEVP,
	--CAST (Point4_vs_RecentTarget_STDEVP AS DECIMAL(19,2)) AS IY4VA_STDEVP,
	--CAST (Point5_vs_RecentTarget_STDEVP AS DECIMAL(19,2)) AS IY5VA_STDEVP,
	--CAST (Point6_vs_RecentTarget_STDEVP AS DECIMAL(19,2)) AS IY6VA_STDEVP,
	--CAST (Point7_vs_RecentTarget_STDEVP AS DECIMAL(19,2)) AS IY7VA_STDEVP,
	--CAST (Point8_vs_RecentTarget_STDEVP AS DECIMAL(19,2)) AS IY8VA_STDEVP,

	CASE WHEN GroupingQOEPoints_College_Divisor = 0 THEN NULL ELSE GroupingQOEPoints_College_Divisor END AS CountofEnrolsWithQOE_All
	,CASE WHEN GroupingQOEPoints_GCSE_College_Divisor = 0 THEN NULL ELSE GroupingQOEPoints_GCSE_College_Divisor END AS CountofEnrolsWithQOE_GCSE
	,GroupingEnrolmentPoints_Divisor AS CountofEnrolsWithEnrolmentPoints,
--	,GroupingVAScore_L3VAStu_NumEnrols AS CountofEnrolsWithDfEVA

	CountofEnrolsWithIYPoints,
	CountOfEnrol - CountofEnrolsWithIYPoints AS CountofEnrolsWithMissingIYPoints,
	CountofEnrolsWithEnrolmentPoints,
	CountOfEnrol - CountofEnrolsWithEnrolmentPoints AS CountofEnrolsWithMissingEnrolPoints

	FROM #TempFinal


GO
